How To Use Sumif In Google Sheets

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

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.