Excel SUMIF function

Summary

The Excel SUMIF function sums up the values in a range that meet a specified criterion. It’s commonly used for conditional summing based on a single criterion.
Syntax
				
					=SUMIF(range, criteria, [sum_range])
				
			
  • range: The range of cells you want evaluated by criteria
  • criteria: The condition or criteria in the form of a number, expression, or text that defines which cells will be added
  • [sum_range]: [Optional] The actual cells to sum. If omitted, the cells in range are summed
Return value
The sum of cells that meet the criteria.

How to use

SUMIF is useful for summing cells that match a single condition. It’s often used in budgeting, financial analysis, and inventory management to sum values based on specific criteria.

Examples

Simple SUMIF
Summing Based on a Single Criterion: To sum the values in B2:B10 where the corresponding cells in A2:A10 equal “Apples”:
				
					=SUMIF(A2:A10, "Apples", B2:B10)
				
			
This formula sums the values in B2:B10 for rows where A2:A10 contains “Apples”.
SUMIF with Numeric Criteria
Summing Values Greater Than a Certain Number: To sum values in C2:C10 that are greater than 100:
				
					=SUMIF(C2:C10, ">100")
				
			
This formula adds up all values in C2:C10 that are greater than 100.
SUMIF with Cell Reference Criteria
Using Cell Reference for Criteria: Summing based on criteria in another cell:
				
					=SUMIF(D2:D10, ">" & E1, F2:F10)
				
			
If E1 contains the number 50, this formula sums values in F2:F10 where D2:D10 is greater than 50.
SUMIF for Date Ranges
Summing Values for a Specific Date Range: To sum values for a specific date range in G2:G10:
				
					=SUMIF(H2:H10, ">=" & DATE(2022,1,1), G2:G10)
				
			
This formula sums the values in G2:G10 where dates in H2:H10 are on or after January 1, 2022.
Running Sum with SUMIF
Dynamic Running Total Based on Criteria: Generating a running sum based on a condition:
				
					=SUMIF($A$2:A2, "Apples", $B$2:B2)
				
			
This formula provides a running total in each row for values in B2:B10 where A2:A10 is “Apples”.

Additional Notes

  • SUMIF is highly effective in scenarios requiring conditional summing based on specific criteria in datasets.
  • The criteria can be a number, expression, cell reference, or text string.
  • For multiple criteria, consider using SUMIFS, which is an extension of SUMIF.

Related Functions

Excel SUMIFS function

The Excel SUMIFS function sums numbers based on multiple criteria, enhancing detailed and conditional data aggregation.

Excel COUNTIF function

The Excel COUNTIF function counts cells that meet a single condition, vital for targeted data quantification.

Excel SUMPRODUCT function

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

Excel SUM function

The Excel SUM function adds together numbers, fundamental for financial totals and aggregating data.

Content Navigation