SUMIF function

The SUMIF function in Excel adds the numbers in a range that meet a specified condition (criteria). It is commonly used when you want to sum values based on a specific condition, such as summing sales for a particular product, or summing expenses for a certain category.

Syntax

=SUMIF(range, criteria, [sum_range])

Parameters

1. range (required): The range of cells that you want to apply the criteria to. These cells are checked to see if they meet the condition.
2. criteria (required): The condition that must be met. This can be a number, text, logical expression, or a cell reference.
3. [sum_range] (optional): The actual cells to sum. If omitted, Excel sums the cells in the range.

Key Points

1. Criteria: The criteria can be:
   • A number (e.g., 5, 100)
   • A text string (e.g., "apple", "A*")
   • A logical expression (e.g., ">100", "<>0")
   • A cell reference (e.g., A1), which allows you to dynamically change the criteria.
2. Sum Range: The sum_range allows you to sum values from a different range than the one you’re applying the criteria to. If this argument is omitted, Excel sums the values in the range itself.
3. Wildcards in Criteria:
   • * (asterisk) for any sequence of characters.
   • ? (question mark) for any single character.

Examples

1. Sum all values in range B1:B10 where corresponding values in A1:A10 are greater than 100:

   =SUMIF(A1:A10, ">100", B1:B10)
   

   Result: Adds all values in the range B1:B10 where the corresponding value in A1:A10 is greater than 100.

2. Sum all values in range B1:B10 where the value in A1:A10 is exactly “Apple”:

   =SUMIF(A1:A10, "Apple", B1:B10)
   

   Result: Adds all values in B1:B10 where the corresponding value in A1:A10 is “Apple”.

3. Sum all values in range B1:B10 where the corresponding value in A1:A10 is not equal to zero:

   =SUMIF(A1:A10, "<>0", B1:B10)
   

   Result: Adds all values in B1:B10 where the corresponding value in A1:A10 is not equal to zero.

4. Sum values in range B1:B10 where the corresponding value in A1:A10 starts with the letter “A”:

   =SUMIF(A1:A10, "A*", B1:B10)
   

   Result: Adds all values in B1:B10 where the corresponding value in A1:A10 begins with the letter “A”.

5. Sum values in range B1:B10 where the corresponding value in A1:A10 is greater than or equal to the value in cell C1:

   =SUMIF(A1:A10, ">=" & C1, B1:B10)
   

   Result: Adds all values in B1:B10 where the corresponding value in A1:A10 is greater than or equal to the value in cell C1.

Notes

• Empty Cells: If the range or sum_range includes empty cells, they are ignored in the calculation.
• Non-Numeric Criteria: If the criteria involves text or logical operators, ensure they are enclosed in quotation marks (" ").
• Logical Operators: When using logical operators like >, <, >=, <=, or <> in the criteria, they must be enclosed in quotation marks (e.g., ">100").

Related Functions

• SUMIFS: The SUMIFS function adds the numbers in a range that meet multiple criteria. This is useful if you want to apply more than one condition to your summation.
• COUNTIF: Similar to SUMIF, but counts the number of cells that meet a specific condition, rather than summing them.
• AVERAGEIF: Averages the numbers in a range that meet a specific condition.
• SUM: Adds up a range of values without any conditions.

The SUMIF function is particularly useful for performing conditional summations, making it widely used in financial, statistical, and data analysis tasks.