Method 1 – Combining SUMIF Functions to Apply Multiple Criteria in Excel
The SUMIF function only takes a single condition. Add multiple SUMIF functions under different conditions. This means that we are summing up multiple criteria.
The Generic Formula:
=SUMIF(range,criteria,sum_range)+SUMIF(range,criteria,sum_range)+........
Find the Total Sales of John and Stuart in January.
Steps:
- Type the following formula in Cell I5:
=SUMIF(C5:C12,H5,D5:D12)+SUMIF(C5:C12,H6,D5:D12)
- Press Enter.
- You found the Total Sales of John and Stuart in January.
- You can add more criteria with this method. Easily find the total sales of any person or product.
Breakdown of Formula
➤ SUMIF(C5:C12,H5,D5:D12)
This function sums up John’s sale in January and returns 1200.
➤ SUMIF(C5:C12,H6,D5:D12)
This function sums up Stuart’s sale in January and returns 700.
➤ SUMIF(C5:C12,H5,D5:D12)+SUMIF(C5:C12,H6,D5:D12)
This formula adds those values and returns $1900.
Method 2 – Joining SUMPRODUCT with SUMIF Function for Multiple Criteria
Use the SUMIF function with multiple criteria with the SUMORODUCT function. The SUMPRODUCT function returns the sum of the products of similar ranges or arrays. The default method is multiplication, but add, subtract, and divide with this function. Use the multiple criteria as a range in the SUMIF function. Return an array. The SUMPRODUCT function will add those arrays and return results.
The Generic Formula:
=SUMPRODUCT(SUMIF(range,criteria_range,sum_range))Find the Total Sales of all the salespeople in March.
Steps:
- Type the following formula in Cell J5:
=SUMPRODUCT(SUMIF(C5:C12,I5:I7,F5:F12))
- Press Enter.
- You successfully found the total sales of all the salespeople in March.
- This formula is more efficient than the previous one if your criteria come from a particular column.
- You can easily pass those criteria as a range in the SUMIF function.
Breakdown of Formula
➤ SUMIF(C5:C12,I5:I7,F5:F12)
This function will return an array: {1300;700;1200}
➤ SUMPRODUCT(SUMIF(C5:C12,I5:I7,F5:F12))
SUMPRODUCT will add those arrays and return $3200.
Method 3 – Merging Excel SUM and SUMIF Functions to Apply Multiple Criteria
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 (+).
The Generic Formula:
=SUM(SUMIF(range,criteria2,sum_range1),SUMIF(range,criteria2,sum_range2).......)Find the total sales of the product Book in February and the total sales of the product Pen in January.
Steps:
- Type the following formula in Cell I5:
=SUM(SUMIF(B5:B12,H5,E5:E12),SUMIF(B5:B12,H6,D5:D12))
- Press Enter.
- See, we are successful in finding the total sales of the product Book in February and the total sales of the product Pen in January.
Breakdown of Formula
➤ SUMIF(B5:B12,H5,E5:E12)
This function will return 1000.
➤ SUMIF(B5:B12,H6,D5:D12)
This function will return 1400.
➤ SUM(SUMIF(B5:B12,H5,E5:E12),SUMIF(B5:B12,H6,D5:D12))
The SUM function will add those values and return $2400.
Excel SUMIFS Function for Multiple Criteria
To sum up multiple criteria, you can use the SUMIFS function. If you don’t want to use complex methods or formulas, this function will easily work. This function sums 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 | The range of cells has to be summed up 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 meets all given criteria.
- Available in Version:
Office 365 ￭ Excel 2019 ￭ Excel 2016 ￭ Excel 2013 ￭ Excel 2011 for Mac ￭ Excel 2010 ￭
Excel 2007.
Find the total sales of Stuart in January greater than $150.
Steps:
- Type the following formula in Cell I7:
=SUMIFS(D5:D12,C5:C12,I4,D5:D12,">150")
- Press Enter.
- You were successful in finding Stuart’s total sales in January greater than $150.
SUMIF vs. SUMIFS in Excel
- The SUMIF function allows only one criterion, while SUMIFS can handle multiple principles.
- In SUMIF, the range and sum_range arguments are separate, while in SUMIFS, the sum_range is the first argument followed by pairs of criteria ranges and criteria.
- SUMIFS requires all rules to be met simultaneously, whereas SUMIF evaluates each criterion individually.
- SUMIFS offers more flexibility in specifying multiple criteria, making it suitable for complex conditions.
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 optimal.
Frequently Asked Questions
1. Can I combine different criteria types, such as text and numbers, in the “SUMIF” function?
Yes, you can combine different rules, such as text and numbers, in the SUMIF function. Excel will evaluate the criteria based on their respective data types.
2. Are there any limitations or considerations when using SUMIF with multiple criteria?
When using SUMIF with multiple rules, keep in mind the following considerations:
- Each range and criterion pair should be in the same order.
- The number of ranges and criteria should be the same.
- The sum_range (range to be summed) must be the last argument in the formula.
3. How do I sum values in Excel based on multiple rules using the “SUMIF” function?
To sum values in Excel based on multiple rules using the SUMIF function, you must provide alternating ranges and criteria pairs in the formula. The function will only sum the values that meet all the specified criteria.
Download Practice Workbook
Download this practice workbook.
SUMIF Multiple Criteria: Knowledge Hub
- How to Apply SUMIF with Multiple Ranges in Excel
- SUMIF Across Multiple Sheets in Excel
- SUMIF with Multiple Criteria in Different Columns in Excel
- How to Sum Based on Column and Row Criteria in Excel
- SUMIF with Or Criteria in Excel
<< Go Back to Excel SUMIF Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
