Excel SUMIFS function

Summary

The Excel SUMIFS function is used to sum cells that meet multiple criteria. It’s an extension of the SUMIF function and can apply various conditions across different ranges.
Syntax
				
					=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
				
			
  • sum_range: The range of cells to sum
  • criteria_range1: The first range to evaluate with the associated criteria
  • criteria1: The condition to be met in criteria_range1
  • [criteria_range2, criteria2], …: [Optional] Additional ranges and their corresponding criteria
Return value
The sum of cells that meet all the criteria.

How to use

SUMIFS is ideal for summing cells based on multiple conditions. It’s used extensively in data analysis, financial modeling, and inventory management for conditional summing.

Examples

Simple SUMIFS
Summing with Multiple Conditions: To sum values in A2:A10 where B2:B10 is “Apples” and C2:C10 is “Large”:
				
					=SUMIFS(A2:A10, B2:B10, "Apples", C2:C10, "Large")
				
			
This formula sums the values in A2:A10 for rows where B2:B10 equals “Apples” and C2:C10 equals “Large”.
SUMIFS with Numeric Criteria
Summing Values Based on Numeric Criteria: Sum values in D2:D10 where E2:E10 is greater than 100 and F2:F10 is less than 200:
				
					=SUMIFS(D2:D10, E2:E10, ">100", F2:F10, "<200")
				
			
Adds up values in D2:D10 that meet both conditions in E2:E10 and F2:F10.
SUMIFS with Date Criteria
Summing Values Within a Date Range: To sum values for dates within a specific range in G2:G10:
				
					=SUMIFS(G2:G10, H2:H10, ">=1/1/2022", H2:H10, "<=12/31/2022")
				
			
Sums the values in G2:G10 for dates in 2022 in H2:H10.
SUMIFS across Different Criteria
Combining Different Types of Criteria: Summing values based on text and numeric conditions:
				
					=SUMIFS(I2:I10, J2:J10, ">=10", K2:K10, "Active")
				
			
Calculates the sum in I2:I10 where J2:J10 is equal or greater than 10 and K2:K10 is “Active”.
Dynamic SUMIFS for Running Totals
Running Total with Multiple Conditions: Creating a running sum based on multiple criteria:
				
					=SUMIFS($L$2:L2, $M$2:M2, "Yes", $N$2:N2, ">5")
				
			
Provides a running total in each row for L2:L10 where M2:M10 is “Yes” and N2:N10 is greater than 5.

Additional Notes

  • SUMIFS is highly versatile for complex conditional summing scenarios
  • Criteria can be numbers, expressions, cell references, or text strings
  • Unlike SUMIF, in SUMIFS, the sum_range comes first, followed by pairs of criteria ranges and criteria

Related Functions

Excel SUMPRODUCT function

The Excel SUMPRODUCT function multiplies arrays and sums the result, crucial for complex calculations and data analysis.

Excel SUMIF function

The Excel SUMIF function sums numbers based on a single condition, perfect for conditional totals in data analysis.

Content Navigation