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.
Table of Contents
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.
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
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:
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.
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’.
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.
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.
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:
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
- 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.
- 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).
- Type open parenthesis ‘(‘ or simply hit Tab key to let you use that function.
- 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’.
- 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 ‘,’.
- 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 (“”).
- 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’.
- 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.
- Copy the formula down to cells I3 and I4.
- In cell I7, repeat steps 1-8. This time, for steps 4 and 6, type in ‘C:C’ and ‘H7’, respectively.
- 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.
- Copy the formula down to cell I8.
- In cell I11, repeat steps 1-8. This time, for steps 4 and 6, type in ‘D:D’ and ‘H11’, respectively.
- 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.
- Copy the formula down to cells I12 and I13.
- 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 (“”).
- 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.
- Copy the formula down to cell I17 and change the second argument to ‘>6’. Don’t forget to enclose it with quotation marks (“”).
- 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.