Check Sheet Logo
Check Sheet

SUMIF

The SUMIF formula is used to add up values in a range that meet a specific criterion. It can be used to sum values based on text, numbers, or dates. The formula is most commonly used in financial analysis, budgeting, and data analysis.

Usage

Use the SUMIF formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:

=SUMIF(range, criterion, [sum_range])
Parameters:
  1. range (required):
    The range of cells to evaluate for the criterion.
  2. criterion (required):
    The criterion used to determine which cells to add. This can be a number, text, or a logical expression.
  3. sum_range (optional):
    The range of cells to sum. If this parameter is not provided, the formula will sum the cells in the range parameter.

Examples

Here are a few example use cases that explain how to use the SUMIF formula in Google Sheets.

Summing values by category

Suppose you have a list of sales transactions and you want to know the total sales by product category. You can use the SUMIF formula to sum the sales for each category.

Calculating commissions

If you have a list of sales transactions and you want to calculate the commission earned by each salesperson, you can use the SUMIF formula to sum the sales for each salesperson and then multiply by the commission rate.

Calculating averages

You can use the SUMIF formula to calculate the total number of items sold and then divide by the number of transactions to get the average number of items per transaction.

Common Mistakes

SUMIF not working? Here are some common mistakes people make when using the SUMIF Google Sheets Formula:

Incorrect range

Users may select an incorrect range for the SUMIF formula, resulting in either an incorrect sum or an error. Make sure the range selected includes all the values that need to be summed.

Incorrect criterion

Users may enter an incorrect criterion for the SUMIF formula, resulting in either an incorrect sum or an error. Make sure the criterion matches the values in the range selected.

Missing sum_range

Users may forget to include the optional sum_range argument in the SUMIF formula, resulting in an error. If not sum_range is needed, leave it blank.

Multiple criteria

Users may try to enter multiple criteria in the criterion argument, which is not allowed in SUMIF. Use SUMIFS for multiple criteria.

Mixed data types

Users may have mixed data types in the range or sum_range, resulting in an error. Make sure all values in the range and sum_range are of the same data type.

Related Formulas

The following functions are similar to SUMIF or are often used with it in a formula:

  • SUM

    The SUM function in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.

  • AVERAGE

    The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.

  • COUNTIF

    The COUNTIF formula counts the number of cells within a specified range that meet a certain criterion. This formula is commonly used to count cells that meet a specific condition or criteria.

  • IFERROR

    The IFERROR formula is used to check whether a specified value results in an error or not. If the value results in an error, then it returns a user-specified value instead of the error. This function is commonly used to prevent errors from breaking a formula or to replace error messages with custom messages.

  • VLOOKUP

    The VLOOKUP function is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.

Learn More

You can learn more about the SUMIF Google Sheets function on Google Support.