SUMIFformula 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.
- How to use
- Examples of using
SUMIFformula not working?
- Similar formulas to
SUMIF formula with the syntax shown below, it has 2 required parameters and 1 optional parameter:
- range (required):
The range of cells to evaluate for the criterion.
- criterion (required):
The criterion used to determine which cells to add. This can be a number, text, or a logical expression.
- 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.
ExamplesHere are a few example use cases that explain how to use the
SUMIFformula 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.
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.
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.
SUMIFnot working? Here are some common mistakes people make when using the
SUMIFGoogle Sheets Formula:
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.
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.
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.
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.
The following functions are similar to
SUMIF or are often used with it in a formula:
SUMfunction in Google Sheets adds up a range of numbers. This function is most commonly used to sum the values in a range of cells.
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.
COUNTIFformula 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.
IFERRORformula 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.
VLOOKUPfunction 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.
You can learn more about the
SUMIF Google Sheets function on Google Support.