Excel SUMIF Function for Multiple Criteria (3 Easy Methods)

While working with large amounts of data in Excel, sometimes you need to sum a column with criteria. In order to sum with criteria, we use the SUMIF function. Now, if you find yourself in a situation where you need to sum a column or row with multiple criteria, you can also use the SUMIF function. So, in this tutorial, we are going to show you how to use the SUMIF function with multiple criteria in Excel.


Download Practice Workbook

Download this practice workbook.


Overview of Excel SUMIF Function

  • Summary:

The SUMIF function sums the values in a range that meets the criteria that you specify. We Use the SUMIF function in Excel to sum cells based on numbers that meet specific criteria.

  • Syntax:

The syntax of the SUMIF function is as follows:

=SUMIF (range, criteria, [sum_range])

  • Arguments:
Argument Required/Optional Explanation
range Required The range of cells that we want to be evaluated by criteria.
criteria Required The criteria are in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.
sum range Optional The actual cells to add if we need to combine cells other than those defined in the range argument.
  • Return Value:

This application returns the sum of cells based on numbers that meet specific criteria.

  • Available in Version:

Office 365  ■ Excel 2019 ■ Excel 2016  ■  Excel 2013  ■ Excel 2011 for Mac ■  Excel 2010 ■

Excel 2007.


3 Effective Methods to Use SUMIF with Multiple Criteria in Excel

Now, you can’t directly use the SUMIF function with multiple criteria. Basically, the SUMIF function takes only a single condition. But, we can use the SUMIF function with other functions or methods to solve our problem. To demonstrate this tutorial, we are going to use the following dataset.

sample dataset for Excel SUMIF Function for Multiple Criteria

We are going to find total sales based on some criteria using the SUMIF function. In order to use the SUMIF function with multiple criteria, we are providing you with 3 methods that can truly solve your problem.


1. Combine SUMIF Functions to Apply Multiple Criteria

Now, the first one is pretty simple to use. As we know, the SUMIF function only takes a single condition. So, we can add multiple SUMIF functions with different conditions. It means we are summing up multiple criteria. Therefore, follow the below steps.

The Generic Formula:

=SUMIF(range,criteria,sum_range)+SUMIF(range,criteria,sum_range)+........

Now, we are going to find the Total Sales of John and Stuart in January.

📌 Steps:

  • First, type the following formula in Cell I5:
=SUMIF(C5:C12,H5,D5:D12)+SUMIF(C5:C12,H6,D5:D12)

adding multiple sumif for multiple criteria

  • Then, press Enter.

result of adding multiple sumif to demonstrate Excel SUMIF Function for Multiple Criteria

  • Consequently, as you can see, we have successfully found the Total Sales of John and Stuart in January.
  • Therefore, you can add more criteria with this method. And easily find the Total Sales of any person or any Product.

🔎 Breakdown of Formula

SUMIF(C5:C12,H5,D5:D12)

Firstly, this function sums up John’s sale in January and returns 1200.

SUMIF(C5:C12,H6,D5:D12)

Then, this function sums up Stuart’s sale in January and returns 700.

SUMIF(C5:C12,H5,D5:D12)+SUMIF(C5:C12,H6,D5:D12)

Finally, our formula adds those values and returns $1900.

Read More: How to SUM with IF Condition in Excel (6 Suitable Examples)


2. Join SUMPRODUCT with SUMIF Function for Multiple Criteria 

Now, another way to use the SUMIF function with multiple criteria is using it with the SUMORODUCT function. The SUMPRODUCT function returns the sum of the products of similar ranges or arrays. The default method is multiplication, but you can also add, subtract and divide with this function. Now, we will use the multiple criteria as a range in the SUMIF function. After that, it will return an array. Then, our SUMPRODUCT function will add those arrays and return results. So, learn the following process.

The Generic Formula:

=SUMPRODUCT(SUMIF(range,criteria_range,sum_range))

Now, we want to find the Total Sales of all the salespeople in March.

📌 Steps:

  • First, type the following formula in Cell J5:
=SUMPRODUCT(SUMIF(C5:C12,I5:I7,F5:F12))

sumproduct function with sumif for multiple criteria

  • Then, press Enter.

Excel SUMIF Function for Multiple Criteria

  • Hence, as you can see, we are successful in finding the total sales of all the salespeople in March.
  • In our opinion, this formula is more efficient than the previous one if your criteria come from a particular column.
  • Finally, you can easily pass those criteria as a range in the SUMIF function.

🔎 Breakdown of Formula

SUMIF(C5:C12,I5:I7,F5:F12)

First of all, this function will return an array: {1300;700;1200}

SUMPRODUCT(SUMIF(C5:C12,I5:I7,F5:F12))

Finally, SUMPRODUCT will add those arrays and return $3200.

Read More: SUMIF Multiple Ranges [6 Useful Ways]


Similar Readings


3. Combine SUM and Multiple SUMIF Functions

Now, by combining the SUM and SUMIF functions we can perform this with multiple criteria. This function is almost similar to the 1st method. The basic difference is we are using the SUM function instead of the plus sign (+). Therefore, learn the following steps.

The Generic Formula:

=SUM(SUMIF(range,criteria2,sum_range1),SUMIF(range,criteria2,sum_range2).......)

We want to find the total sales of the product Book in February and total sales of the product Pen in January.

📌 Steps:

  • First of all, type the following formula in Cell I5:
=SUM(SUMIF(B5:B12,H5,E5:E12),SUMIF(B5:B12,H6,D5:D12))

formula using SUM and SUMIF functions together

  • Then, press Enter.

result of SUM and SUMIF functions together

  • Finally, you can see we are successful in finding the total sales of the product Book in February and total sales of the product Pen in January.

🔎 Breakdown of Formula

SUMIF(B5:B12,H5,E5:E12)

Firstly, this function will return 1000.

SUMIF(B5:B12,H6,D5:D12)

Then, this function will return 1400.

SUM(SUMIF(B5:B12,H5,E5:E12),SUMIF(B5:B12,H6,D5:D12))

Finally, the SUM function will add those values and return $2400.


Excel SUMIFS Function for Multiple Criteria

Now, this part is a bonus for you. In order to sum up multiple criteria, you can use the SUMIFS function. This function takes multiple criteria. If you don’t want to use complex methods or formulas, this function will work with ease. This function sum cells that meet multiple criteria.

The Basic Syntax of the SUMIFS Function:

  • Function Objective:

Add the cells given by specified conditions or criteria.

  • Syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

  • Arguments Explanation:
Arguments Required/Optional Explanation
sum_range Required Range of cells that has to be summed under conditions or criteria.
criteria_range1 Required Range of cells where the criteria or condition will be applied.
criteria1 Required Condition for the criteria_range1.
[criteria_range2] Optional 2nd range of cells where the criteria or condition will be applied.
[criteria2] Optional Condition or criteria for the criteria_range2 
  • Return Value:

The sum of the cells in a numeric value meeting all given criteria.

  • Available in Version:

Office 365  ■ Excel 2019 ■ Excel 2016  ■  Excel 2013  ■ Excel 2011 for Mac ■  Excel 2010 ■

Excel 2007.

Now, We want to find the total sales of Stuart in January greater than $150.

📌 Steps:

  •  First, type the following formula in Cell I7:
=SUMIFS(D5:D12,C5:C12,I4,D5:D12,">150")

SUMIFS function with multiple criteria

  • Then, press Enter.

result of SUMIFS function

  • In the end, we are successful in finding the total sales of Stuart in January greater than $150.

💬 Things to Remember

✎ Firstly, if you are working with a small dataset and single criteria then the SUMIF function will be efficient.

✎ Secondly, if you are working with a large dataset and complex criteria, using the SUMIFS function may be the optimal way.


Conclusion

To conclude, I hope you found this tutorial helpful to use SUMIF with multiple criteria. We recommend you learn and apply all these methods to your dataset. Definitely, it will improve your knowledge. Therefore, download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.


Related Articles

A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo