Embedded Files
The SUMIF function in Google Sheets is used to sum values across a range of data depending on a conditional test. The SUMIF function only adds values to the total when the conditional test is true.
SYNTAX: =SUMIF(range, criterion, [sum_range])
range: The data that you want to test the condition on.
criterion: The condition that you want to test. SUMIF only allows one criterion, SUMIFS is a more advanced function that allows the user to test multiple functions.
[sum_range]: The values that should be added if the criterion is true.
The criterion in a SUMIF function can be numerical, a cell, or text. If you use text values, you need to wrap the expression in quotation marks.
The best way to look at this function is through a specific example. In the data below, we want to answer the question: How much did the person spend on groceries?
The data shows 6 instances where the person spent money on groceries. We could sum each of those values individually but that would be time consuming and there is room for human error (missing a cell, etc). In this data set there are also only 23 rows of data which is rather manageable. If the data had thousands of rows, it would be very inefficient to scroll and find all of the instances where the person paid for groceries.
Instead, we can use the SUMIF function!
We want to tell the function to add the values in column B whenever column A says "Groceries". But first, we need to set up a space to do this work.
It is best practice to label work clearly in sheets so that you or anyone else that will be using or reviewing the data at a later date can easily understand the steps that you took and the logic that you used.
To make your thinking clear:
Label column C: Total Spent
In C2, type Groceries
To use the SUMIF function to total the amount spent on Groceries:
Click in cell D2
Type =SUMIF(
The first statement is the range that you want to look for your criterion. In this case, that is the different categories in column A. So you should type A2:A23. You always separate statements by commas.
The next part of the statement is your criterion or what you are looking for. In this case you want to find all the times the person paid for Groceries. You have to options:
Type: "Groceries"
Type: C2 -> you can use this cell because when you set up your clear thinking, you typed "Groceries" in cell C2. I always recommend using a cell rather than typing words because it reduces error points and makes your formulas more dynamic.
The final statement is the values you want to sum if the statement is true. In this case, you want to add the values in column B. So you should type B2:B23
Hit ENTER
Use this link to see additional examples and uses for the SUMIF function.
Page updated
Google Sites
Report abuse