Excel SUMIFS with Multiple Vertical and Horizontal Criteria

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


Download Practice Workbook

You can download the practice workbook from the following download button.


3 Ways of Using SUMIFS Function in Excel with Multiple Vertical and Horizontal Criteria

We have taken a set of data that has the Product, Customer or Supplier, Date, and Price of different persons.

A set of product, customer, date and price data in Excel


1. Apply SUMIFS with Multiple Criteria Vertically

In the first methodology, we will take 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 the value that is less than 22 in the range of E5:E13
  • Now, hit the ENTER button.

Applying SUMIFS with Multiple Criteria Vertically

So, here is your sum result. Check it out!

Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria


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

Combining OFFSET, MATCH and COUNT Functions with SUMIFS to Match Multiple Horizontal and Vertical Criteria

Read More: How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows


2.2 Combining INDEX-MATCH with SUMIFS

After using SUMIFS with OFFSET function, we will observe a little 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.

Combining INDEX-MATCH with SUMIFS to Match Multiple Horizontal and Vertical Criteria

Read More: SUMIFS with INDEX-MATCH Formula Including Multiple Criteria


Similar Readings


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.

SUMIFS with Multiple Criteria for Multiple Columns to Sum

Read More: How to Use SUMIFS Function in Excel with Multiple Criteria


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

The SUMIFS function is logic based function and the SUMPRODUCT function is a mathematical-based function. In this method, we will show a very simple example for SUMPRODUCT function to do the same job as SUMIFS.

πŸ“Œ Steps:

In this method, we will add all the price values John that is 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.

Using an Alternative to SUMIFS for Simultaneous Vertical and Horizontal Criteria in Excel

Read More: [Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)


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 of our blog ExcelDemy.


Related Articles

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, i am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo