Excel is essential in our everyday workdays. Excel users have to add up various values against particular criteria for the sake of data manipulation. We are required to use the SUMIFS function when doing that kind of addition in accordance with our data. In this article, we will evaluate the use of Excel SUMIFS with multiple criteria vertical and horizontal.
SUMIFS Multiple Criteria Vertical and Horizontal: 3 Ways of Using
We have taken a set of data that has the Product, Customer or Supplier, Date, and Price of different persons.
1. Apply SUMIFS with Multiple Criteria Vertically
In the first methodology, we will take the SUMIFS function in its very basic form with 2 criteria: Customer- John and Price- less than $ 22.
📌 Steps:
- Go to cell D17 and apply the following formula:
=SUMIFS(E5:E13,C5:C13,D15,E5:E13,"<"&D16)Here,
- 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
- Now, hit the ENTER button.
So, here is your sum result. Check it out!
Read More: How to Use SUMIFS with Multiple Criteria in the Same Column
2. Combine Other Excel Functions with SUMIFS to Match Multiple Horizontal and Vertical Criteria
Now, Excel SUMIFS cannot look up matching values for horizontal and vertical criteria simultaneously. So, if you have criteria along rows and columns at a time, you can’t use the SUMIFS function alone for multiple matches and sums. In this section, we have shown two ways of creating a formula to enable SUMIFS to match multiple horizontal and vertical criteria and sum up the respective values.
2.1 Combining SUMIFS, OFFSET, MATCH and COUNT Functions
Instead of using the basic form of SUMIFS, we will observe a little bit of a complex combination with OFFSET, MATCH, and COUNT functions now. These intricate functions will be used to define the first argument of SUMIFS, the sum range.
📌 Steps:
In this method, we will add all the price values 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)Here,
- sum_range=OFFSET(C4,1,MATCH(D16,D4:F4,0),COUNT(F:F),1)
- criteria _range1= Customer column
- criteria1=D15 or March
- Now, hit the ENTER button.
Read More: How to Apply SUMIFS with Multiple Criteria in Different Columns
2.2 Combining INDEX-MATCH with SUMIFS
After using SUMIFS with the OFFSET function, we will observe a slightly less complicated combination with INDEX and MATCH functions now. These functions will be used to define the first argument of SUMIFS, sum_range.
📌 Steps:
In this method, we will add all the price values David that is between the date of 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
- Now, hit the ENTER button.
Read More: How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
3. SUMIFS with Multiple Criteria for Multiple Columns to Sum
Now, Excel SUMIFS cannot look up matching values for horizontal criteria. The sum range argument in the SUMIFS function cannot have multiple columns. For that cause, we will sum up prices horizontally and create a new column named Subtotal. In this section, we will use multiple criteria, to sum up the total prices of a particular Supplier.
📌 Steps:
In this method, we will add all the price values John that is between the date of 02/10/22 and 10/10/22.
- 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)Here,
- 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
- Now, hit the ENTER button.
The SUMPRODUCT Function: Alternative to SUMIFS for Simultaneous Vertical and Horizontal Criteria in Excel
The SUMIFS function is logic logic-based function and the SUMPRODUCT function is a mathematical-based function. In this method, we will show a very simple example of the SUMPRODUCT function to do the same job as SUMIFS.
📌 Steps:
In this method, we will add all the price values 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 a 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 a 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
- Now, hit the ENTER button.
Read More: SUMIFS: Sum Range Across Multiple Columns
Download Practice Workbook
You can download the practice workbook from the following download button.
Conclusion
Follow these steps and stages to SUMIFS multiple criteria vertical and horizontal. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section.
Related Articles
- SUMIFS with Multiple Criteria Along Column and Row in Excel
- Exclude Multiple Criteria in Same Column with SUMIFS Function
- How to Use VBA Sumifs with Multiple Criteria in Same Column
<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
