How to Use SUMIF Function in Google Sheets

The SUMIF function in Google Sheets is useful if you want to get the sum of cells, that meet the provided condition, in a given range.

Meaning, the SUMIF function adds up cells that meet the given criteria.

The rules for using the SUMIF function in Google Sheets are as follows:

  • The SUMIF function only supports a single condition. On the other hand, the SUMIFS function, which we will discuss in our next article, supports multiple conditions. 
  • The criteria can be based on dates, texts, and numbers.
  • The SUMIF function supports logical operators such as (>,<,<>,=) and wildcards (*,?). For more information on how wildcards are used for the SUMIF function, please refer to this article. 
  • If the third argument is omitted, the function will sum up the values from the first argument instead.
  • The function requires a range, and you can’t use an array.
  • Text string in criteria should be enclosed with quotation marks, whereas cell reference shouldn’t.

Let’s take an example.

Armel, a manager of the Sales department, has been preparing reports for the upcoming business review for April.

He has the datasets below and needs to generate a summary for him to put in his presentation.

How to use SUMIF function in Google Sheets

He needs to summarize the values above in such a way that it’s easy to read and understand.

He came up with the following relevant key points

How to use SUMIF function in Google Sheets

With the help of the SUMIF function, which he learned from Sheetaki.com, Armel was able to pull the numbers based on the key points he wanted to show to his presentation.

See his final table below:

How to use SUMIF function in Google Sheets

Pretty smart, right?

Watch out for a more advanced tutorial and examples on how you can use the SUMIF function in the coming weeks. Be sure to subscribe to be notified. 

Perfect! Let’s begin getting to know more about our SUMIF function in Google Sheets.

 

The Anatomy of the SUMIF Function

So the syntax (the way we write) the SUMIF function is as follows:

=SUMIF(range, criteria, [sum_range])

Let’s dissect this thing and understand what each of these terms means:

  • = the equal sign is just how we start any function in Google Sheets. It is how Google Sheets understand that we are asking it to either do computation or use a function.
  • SUMIF() this is our SUMIF function. It gets the sum of cells that meet the provided condition.
  • range is the range of cells that we want to apply the criteria against.
  • criteria is the single condition that is used to determine which cells need to be added.
  • [sum_range] is an optional argument, which contains numeric values that are to be added if the corresponding range entry meets the provided criteria.

 

A Real Example of Using SUMIF Function

Let’s take a look at the reports that Armel prepared below to see how the SUMIF function is used in Google Sheets.

In the first report that he prepared, Armel pulled the total sold items per team. He used a cell reference to set the first condition.

=SUMIF(B:B,H3,F:F)

The first argument is column B, where the function will apply the criteria passed to the second argument. 

Notice in cell H3, the reference used for the second argument, the text string ‘Team Diamond’ is located. This is our criteria.

Lastly, the third argument is column F, which has the sold item numbers to be added by the SUMIF function.

This means that the SUMIF function will perform addition to cells in column F that corresponds to the cells in column B which have the instances of the text string ‘Team Diamond’.

How to use SUMIF function in Google Sheets

The same logic applies to the SUMIF function used to pull the numbers for the remaining teams.

Now, let’s take a look at the fourth report Armel generated from his table.

He pulled the total item sold for sales representatives whose tenure is less than 6 years and those whose tenure is greater than 6 years.

How to use SUMIF function in Google Sheets

The first argument is column E, where the logical expression ‘<6’ will be applied, and the third argument is column F, to which the function will perform addition. 

This means that the function will perform addition to cells in column F that corresponds to the cells in column E which have values less than 6.

How to use SUMIF function in Google Sheets

The same logic applies to the SUMIF function used to pull the total items sold of sales representatives whose tenure is greater than 6. See below:

How to use SUMIF function in Google Sheets

Notice that on the first report, the second argument is a cell reference to the condition, whereas, in the fourth report, the second argument is a logical expression enclosed with quotation marks.

You may make a copy of the spreadsheet using the link I have attached below.

How to Use SUMIF Function in Google Sheets

  1. Click on any cell to make it the active cell. For this guide, I will be selecting I2, where I want to show the result.

How to use SUMIF function in Google Sheets

 

  1. Next, type the equal sign ‘=‘ to begin the function and then follow it with the name of the function, which is our ‘sumif‘ (or ‘SUMIF‘, not case sensitive like our other functions).

 

  1. Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.

How to use SUMIF function in Google Sheets

 

  1. Now the exciting part! Let’s give our function its first argument, the range. Click the range or column that you want to use. In this case, click on column B or type in ‘B:B’.

How to use SUMIF function in Google Sheets

 

  1. To let the Google sheet know that we’re done typing our first argument, we should now type in the delimiter or the character that separates each argument on a function. In this case, type comma,’.

How to use SUMIF function in Google Sheets

 

  1. Type in our second argument, which is the criteria. Type in the text string ‘Team Diamond’. Just be sure to enclose it with quotation marks (“”). Alternatively, you may use a cell address that contains the text ‘Team Diamond’. In this case, you can type in ‘H2’ instead of the text string ‘Text Diamond’. Take note that you don’t have to enclose the cell address to quotation marks (“”).

 

  1. End your second argument by typing in another comma (,). After that, pass the third argument, which is the range where the values to be added are located. Click on the column F or type in ‘F:F’.

How to use SUMIF function in Google Sheets

 

  1. Finally, hit your Enter or Tab key. Cell I2 will now show you the return value of the SUMIF function, or the total items sold of Team Diamond.

How to use SUMIF function in Google Sheets

 

  1. Copy the formula down to cells I3 and I4.

 

  1. In cell I7, repeat steps 1-8. This time, for steps 4 and 6, type in ‘C:C’ and ‘H7’, respectively.


 

  1. Hit your Enter or Tab key. Cell I7 will now show you the return value of the SUMIF function, or the total items sold in EMEA region.

How to use SUMIF function in Google Sheets

 

  1. Copy the formula down to cell I8.

How to use SUMIF function in Google Sheets

 

  1. In cell I11, repeat steps 1-8. This time, for steps 4 and 6, type in ‘D:D’ and ‘H11’, respectively.

How to use SUMIF function in Google Sheets

 

  1. Hit your Enter or Tab key. Cell I11 will now show you the return value of the SUMIF function, or the total items sold by the morning shift.

How to use SUMIF function in Google Sheets

 

  1. Copy the formula down to cells I12 and I13.

How to use SUMIF function in Google Sheets

 

  1. In cell I16, repeat steps 1-8. This time, for steps 4 and 6, type in ‘E:E’ and ‘<6’, respectively. Don’t forget to enclose the second argument with quotation marks (“”).

 

  1. Hit your Enter or Tab key. Cell I16 will now show you the return value of the SUMIF function, or the total items sold by sales representatives whose tenure is less than 6 years.

How to use SUMIF function in Google Sheets

 

  1. Copy the formula down to cell I17 and change the second argument to ‘>6’. Don’t forget to enclose it with quotation marks (“”).

How to use SUMIF function in Google Sheets

 

  1. Hit your Enter or Tab key. Cell I17 will now show you the return value of the SUMIF function, or the total items sold by sales representatives whose tenure is greater than 6 years.

 
That’s pretty much it. You can now use the SUMIF function in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like