# Excel SUMIFS with Multiple Vertical and Horizontal Criteria

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

### Method 1 – Apply SUMIFS with Multiple Criteria Vertically

• Insert the criteria for the customer in D15 and the price in D16.
• Go to cell D17 and apply the following formula:
`=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.

### Method 2 – Combine Other Excel Functions with SUMIFS to Match Multiple Horizontal and Vertical Criteria

#### Case 2.1 – Combining SUMIFS, OFFSET, MATCH, and COUNT Functions

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

• Insert the following formula in cell D17:
`=SUMIFS(OFFSET(C4,1,MATCH(D16,D4:F4,0),COUNT(F:F),1),Customer,D15)`
• sum_range=OFFSET(C4,1,MATCH(D16,D4:F4,0),COUNT(F:F),1)
• criteria _range1= Customer column
• criteria1=D15 or March
• Hit the Enter button.

#### Case 2.2 – Combining INDEX-MATCH with SUMIFS

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

• Go to cell D17 and apply the following formula:
`=SUMIFS(INDEX(B4:G20,,MATCH(C24,B4:G4,0)),B4:B20,">="&C25,B4:B20,"<="&C26)`

Here,

• sum_range=INDEX(B4:G20,,MATCH(C24,B4:G4,0))
• criteria _range1= Customer column
• criteria1=D15 or March
• Hit the Enter button.

### Method 3 – SUMIFS with Multiple Criteria for Multiple Columns to Sum

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)`
• sum_range=\$H\$5:\$H\$13
• criteria _range1= \$C\$5:\$C\$13 or Supplier
• criteria1=\$D\$15 or John
• criteria _range2= \$C\$5:\$C\$13 or Supplier
• criteria2=\$D\$15 or John
• Hit the Enter button.

## 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.

• Go to cell D17 and apply the following formula:
`=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
• Hit the Enter button.

## Related Articles

<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Al Ikram Amit

Al Ikram Amit, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a key Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Amit not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently delivering exceptional, high-quality content that adds significant... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF