Excel SUMIFS with Multiple Vertical and Horizontal Criteria

We’ll use a dataset that has the Product, Customer, Date, and Price for different sales.

=SUMIFS(E5:E13,C5:C13,D15,E5:E13,"<"&D16)

sum_range=E5:E13 or Price column
criteria _range1=C5:C13 or Customer column
criteria1=D15. This argument will find the name of the customer named John in row 5,10,13
criteria _range2=E5:E13 or Price column
criteria2=“<“&D16. This argument will find a value that is less than 22 in the range of E5:E13

Hit the Enter button. You’ll get the sum of all values that conform to the criteria.

We will add all the price values for John that are in the month of March.

=SUMIFS(OFFSET(C4,1,MATCH(D16,D4:F4,0),COUNT(F:F),1),Customer,D15)

We will add all the price values for David that are between 02/01/23 and 10/01/23.

=SUMIFS(INDEX(B4:G20,,MATCH(C24,B4:G4,0)),B4:B20,">="&C25,B4:B20,"<="&C26)

Here,

We will add all the price values for John between 02/10/22 and 10/10/22.

Create a new subtotal column H that will sum the prices in the previous three columns.
Go to cell D17 and apply the following formula:

=SUMIFS($H$5:$H$13,$C$5:$C$13,$D$15,$D$5:$D$13,">="&$D$16,$D$5:$D$13,"<="&$D$17)

The SUMPRODUCT Function: Alternative to SUMIFS for Simultaneous Vertical and Horizontal Criteria in Excel

We will add all the price values for John that are in the month of March.

=SUMPRODUCT((Customer=D15)*(Months=D16),Data)

Here,

array1= (Customer=D15)*(Months=D16) creates an array {0,0,1;0,0,0;0,0,0;0,0,0;0,0,0;0,0,1;0,0,0;0,0,0;0,0,1}
array1= Data creates an array {5.6,5.7,5.9;8.2,8.1,8.3;10.5,10.4,8.1;12.2,12.6,12.8;25,27,29;6.8,6.3,6.1;9.8,9.4,9.8;5.6,5.5,5.9;6.5,6.9,6.8}.
SUMPRODUCT multiply this array that will have values: 5.9+6.1+6.8=18.8

Download the Practice Workbook

SUMIFS Multiple Criteria in Two Directions.xlsx