This guide covers everything you need to know about the Google Sheets SUMIF function, including its definition, syntax, use cases, and how to use it.
What is the SUMIF Function? How Does It Work?
The SUMIF function in Google Sheets returns a conditional sum across a specified range of cells. Essentially, it allows you to add up numbers in a range that meet a specific criterion.
The purpose of the SUMIF function is to make it easier for you to perform conditional sums without manually scanning through rows and columns of data.
How does it work? The SUMIF function operates on three main components: range, criterion, and sum_range. The range refers to the group of cells you want the function to consider. The criterion is the specific condition a cell must meet to be included in the sum. For example, you might specify a criterion that only includes cells containing a number greater than 20, or cells with the text “Paid”. Lastly, the sum_range is the group of cells to be added up, which is optional and, if left unspecified, defaults to the range.
Note that SUMIF is limited to performing conditional sums with a single criterion. If you need to use multiple criteria, use another function, such as DSUM.
For example, if you use SUMIF(A1:A10,”>20″), it will add all the numbers in the range A1 through A10 that are greater than 20. If you use SUMIF(A1:A10,”Paid”,B1:B10), it will add all the numbers in the B1 through B10 range corresponding to cells in the A1 through A10 range that contain the text “Paid”.
Whether you’re managing a budget, tracking sales, or analyzing data, the SUMIF function can simplify your calculations and increase your productivity.
SUMIF Syntax
The syntax and arguments for the SUMIF function in Google Sheets are as follows:
SUMIF(range, criterion, [sum_range])
- range: This is the range of cells that you want to evaluate against a specific criterion. It is a required argument and can be a row, a column, or a set of selected cells.
- criterion: This is the condition or rule that each cell in the range has to meet for it to be included in the sum. The criterion can be expressed as a number, text, or expression. This is also a required argument.
- sum_range: This optional argument lets you specify a different set of cells to add up, if you have a condition met in your original range of cells. If you don’t provide this, the SUMIF function will just add up the cells in the original range that meet your condition.
Usage notes related to the syntax and arguments of the SUMIF function:
- The range and sum_range arguments need not be the same size but must have the same number of rows and columns. If they don’t, Google Sheets will return an error.
- The criterion argument can contain wildcard characters. The question mark (?) matches any single character while the asterisk (*) matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
- The SUMIF function in Google Sheets can only perform conditional sums with a single criterion. If you need to use multiple criteria for your conditional sum, use the DSUM function.
- The SUMIF function is not case-sensitive. So, if your criterion is a text string, it will match cells that contain the same string in any combination of uppercase and lowercase letters.
- If the criterion argument is a text string, it must be enclosed in quotation marks. If it’s a numerical value or an expression, it does not need to be enclosed in quotation marks.
Examples of How to Use the SUMIF Function
Here are some practical examples of how to use the SUMIF function in Google Sheets. These examples will illustrate how this function can be used to add up cells that meet certain criteria.
Example #1: Summing up sales for a specific product
Suppose you have a sales report with the product names in column A and quantities sold in column B, and you want to find out the total quantity sold for a specific product, say “Product X”. You can use the SUMIF function as follows:
=SUMIF(A2:A100, “Product X”, B2:B100)
This formula sums up the quantities in column B, but only for the rows where the product name in column A is “Product X”.
Example #2: Summing up expenses exceeding a certain amount
Let’s say you have a list of expenses in column A and want to find out the total of the expenses that exceeded $500. You can use the SUMIF function as follows:
=SUMIF(A2:A100,”>500″)
This formula will add up all the values in the range A2:A100 greater than 500.
Example #3: Summing up cells based on criteria in another cell
Suppose you have a list of employees in column A and their sales in column B, and you want to find out the total sales of a specific employee, say “John”. You can use the SUMIF function as follows:
=SUMIF(A2:A100,”John”,B2:B100)
This formula will sum up all the sales in column B where the corresponding employee in column A is “John”.
Example #4: Summing up cells based on multiple criteria
If you want to sum up cells based on multiple criteria, you can use multiple SUMIF functions. For example, if you have a list of products in column A, their sales in column B, and you want to find out the total sales for “Product X” and “Product Y”, you can use the following formula:
=SUMIF(A2:A100,”Product X”,B2:B100) + SUMIF(A2:A100,”Product Y”,B2:B100)
This formula will sum up all the sales for “Product X” and “Product Y”.
Why Is SUMIF Not Working? Troubleshooting Common Errors
If you are using the SUMIF function in Google Sheets and it is not returning the expected results, you might be encountering one of several common errors. These issues can stem from a variety of causes, such as using the incorrect data type or incorrect range references. Understanding these common errors, their causes, and their solutions can help you troubleshoot and resolve these issues more effectively.
#VALUE! Error
Cause: This error typically occurs when the SUMIF function is trying to perform a calculation on non-numeric data. For example, if you are trying to sum up a range that contains text or boolean values, you might see this error.
Solution: To resolve this issue, ensure that the range you are trying to sum contains only numeric data. If the range contains non-numeric data, consider using the IFERROR function to handle these values.
#REF! Error
Cause: This error usually happens when the SUMIF function references a cell or range that doesn’t exist. This can occur if you delete a cell or range that the function is referencing or if the function is referencing a cell or range outside of the limits of the spreadsheet.
Solution: To fix this error, ensure that all references within your SUMIF function are valid. Update any references to cells or ranges that have been deleted, and ensure that all references are within the limits of the spreadsheet.
#N/A Error
Cause: This error often occurs when the SUMIF function can’t find the criterion you’ve specified within your search range. If the function can’t locate the criterion, it will return an #N/A error.
Solution: To resolve this issue, ensure that the criterion you’re using in the SUMIF function actually exists within the range you’re searching for. Also, make sure that the criterion is specified correctly, taking into account any potential case sensitivity or formatting differences.
Error in the Criteria Argument
Cause: This error can occur if the criterion you use in the SUMIF function includes a logical operator (like >, <, =), but the criterion argument is not formatted correctly. For example, if you’re trying to sum all values greater than 10 but enter the criteria as “10>”, you’ll see an error.
Solution: To fix this error, make sure that any logical operators are placed before the value in your criteria argument. So instead of “10>”, you should enter “>10”.
Incorrect Range Size Error
Cause: This error happens when the SUMIF function’s sum_range argument and range argument are not the same size. Both ranges need to contain the same number of cells. Otherwise, the function will return an error.
Solution: To resolve this issue, ensure that the sum_range and range arguments in your SUMIF function reference ranges contain the same number of cells. If they don’t, adjust your ranges so that they match.
For more details on the SUMIF function, check out the official documentation at the Google Docs Editors Help Center.
