In this tutorial, you will learn how to use sumif in Google Sheets.
When you’re working in Google Sheets, you may find yourself needing to sum only certain values in a data set, and not the entire data set. For example, you might want to sum only numbers greater than 100. This is where SUMIF formulas come in handy. SUMIF can be used to sum only values that meet a certain criteria. The criteria can be based on the numerical values themselves, or on values in another range, making SUMIF extremely powerful. Read on to see how to use SUMIF in a couple scenarios
Summing Numbers Larger than 100
Quick Navigation
If you have a set of numbers and want to sum only those that are greater than 100, here’s how:
Step 1
Select the cell you want the sum in and type the following to begin the SUMIF formula: “=SUMIF(”. A popup will appear showing what parameters are needed for SUMIIF.
Step 2
The first parameter is the range to check for the criteria. In this case we’re checking the numbers themselves. Type in the range reference and a comma
Step 3
The next parameter is the criteria to use to check if each value should be added to the sum. For this example, enter “>100”, including the quotes to check if numbers are greater than 100
Step 4
The last parameter is an optional range to perform the sum on for items that meet the criteria. For this example, the two ranges are the same so we don’t need to use this parameter. Type a closing parenthesis and hit enter to finish the formula. The sum will calculate
Summing Based on Text in Another Column
In addition to mathematical checks for numerical values, SUMIIF can be used to check if text matches a certain value
Here’s how:
Step 1
Select a cell and start typing the a SUMIF formula
Step 2
The first parameter will be the range containing the text you want to check for a given value
Step 3
The second parameter is the value you want to check for. All numbers beside this value will be summed
Step 4
For the last, optional, parameter, enter the range with the numbers to sum, then closing parenthesis
Step 5
Hit enter and the sum of all sales made in Philadelphia will calculate
Summary
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to use sumif in Google Sheets. Want more? Check out all the Google Sheets Tutorials.