How to Use SUMIF in Google Sheets

The SUMIF function in Google Sheets gives you more control over which cells you add together than the basic SUM formula. Both actions return a number based on the cells you reference. SUMIF, however, lets you set a single criterion to only add together certain cells in a range.

Here's how to use SUMIF in Google Sheets to quickly filter spreadsheet entries while keeping your information organized.

What Is the SUMIF Function in Google Sheets?

You'll use the SUMIF function if you have a spreadsheet with numerical values but only want to add together some of them. For example, if you have a list of items you've purchased and see how much you've spent on each kind of item, SUMIF can do that for you automatically.

It's possible to just use the SUM function to accomplish this task, but to do that, you need to enter a formula that points to each cell containing a value you want to include. SUMIF lets you write a single formula that looks at the entire set of data and only pick out the ones you want to add together. The function does the parsing for you to save you time. You can also continue adding to your data, and as long as the cells you use still fall into the range SUMIF uses, you don't have to change the formula to keep it current.

Syntax of the SUMIF Function

The SUMIF function has two or three parts, which follow the command =SUMIF. You enter them in this order, with commas between them:

1. Range: The set of information you want the function to examine looking for the criterion.
2. Criterion: The condition that determines whether the function will include a data point in the final sum. You can base the criterion on either text or numbers.
3. Sum Range: The set of numbers SUMIF adds together. If you don't include a sum range, SUMIF will add together the values in the range.

How to Use the SUMIF Function in Google Sheets

This example uses a sample spreadsheet with the prices of various office supplies. Here's how to set up SUMIF.

1. Enter the data you want to analyze into Google Sheets.

2. Click the cell you want to enter the formula into. This example will use SUMIF to add up the total cost of each different item.

3. Enter the SUMIF formula. In this example, SUMIF will calculate the total cost of each item in Column A. Therefore, the range is everything in Column A, the criterion is the specific type of item in that column, and the sum range is everything in Column B, which contains the price of each item.

   The final formula for this cell, which calculates the total cost of pencils, is:

   =SUMIF(A:A, "Pencils", B:B)
   
   

   Text-based criteria are case-sensitive. A SUMIF function that lists the word "Pencils," for example, will not include incidences of "pencils" (starting with a lowercase letter).

4. Press Enter to run the function. The result will appear in the cell.

5. Repeat these steps, substituting the names of the various items, to complete the calculations.

6. Because this SUMIF function looks at all of Columns A and B, adding more entries automatically updates the totals with no more work necessary.

Criteria and Other Uses for the SUMIF Function

Even though you can only use a single filter for each SUMIF function, it has plenty of practical uses. You can use a variety of different conditions for the criterion. The following table contains some of the symbols you can use for SUMIF and what they mean.

SUMIF is a powerful function that can use most of the tools available in Google Sheets. Along with numerical and text data, you can also use time tags. For example, you can use SUMIF to total up the number of push-ups you do in the morning with the criterion <12:00. To add the ones you did the rest of the day, you'd use the criterion >=12:00.

The function can also use the wildcard symbol (*) to pull partial matches. In the example spreadsheet, you could add up just the money for writing implements by using the criterion pen*, which would pull the results of both pens and pencils.

Criteria can also include cell references. This version of SUMIF is handy if you have a comparative value that might change. For example, you could type 50 into cell B5 and have the function refer to that cell (e.g., >B5), and then change the value in the cell to get different results without having to change the SUMIF function itself.