Excel SUMIFS with Multiple Vertical and Horizontal Criteria

Get FREE Advanced Excel Exercises with Solutions!

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.

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

Applying SUMIFS with Multiple Criteria Vertically

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.

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

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.

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

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.

SUMIFS with Multiple Criteria for Multiple Columns to Sum


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.

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

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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Al Ikram Amit
Al Ikram Amit

Hello, I'm Amit, a BUET graduate with a passion for Excel. Currently, I work as an Excel & VBA Content Developer at ExcelDemy, contributing insightful articles to the blog. I enjoy sharing my knowledge with others and always look for opportunities to grow as an Excel expert. Feel free to reach out if you need assistance or want to discuss the latest trends in Excel. Let's excel together in the world of data manipulation and analysis!

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo