April 18, 2021

How to Use the SUMIF and SUMIFS Function in Google Sheets

Google Sheet spreadsheet
SECTIONS
  1. Adding only the entries that are greater or less than a certain number
  2. Adding only the entries that fall within a certain range
  3. Adding the corresponding values of a certain entry
  4. Adding all the entries except for those with certain value
  5. Adding entries, excluding entries with errors
  6. Adding the entries that have a certain wildcard
  7. Can I type the criterion in a cell and reference it in the formula?

The SUM function of Google Sheets is a simple function that can add a large range of numbers stored in the cells. However, the SUM function doesn’t work when the range contains a string or an error. For this, we can use the SUMIF and SUMIFS functions. This tutorial will show you how to use the SUMIF function to consider these cases.

Adding only the entries that are greater or less than a certain number

You may want to add only the entries whose values are greater than a certain number. For our example, we have a set of 100 numbers ranging from 1 to 20 stored in the range A1:E20. We want to add numbers that are greater than 6. To do so, we get the following formula:

=sumif(A1:E20,">6")

The result looks like this:

SUMIF function adding all entries with values less than 6.
SUMIF function adding all entries with values less than 6.


You can simply flip the sign to add the values of all entries that are less than 6:

=sumif(A1:E20,"<6")

The result looks like this:

SUMIF function adding all entries with values greater than 6.
SUMIF function adding all entries with values greater than 6.


If you wish to include 6 in the values to be added, simply add an equal sign:

=sumif(A1:E20,">=6")

=sumif(A1:E20,"<=6")

To summarize this, for our current use of SUMIF, the syntax looks like this:

=SUMIF(range, “criterion”)

where range is the range of cells where the entries are located, and criterion, which has to be enclosed in quotation marks, is being applied to the same range before they are added. 

Adding only the entries that fall within a certain range

As SUMIF only works with a single argument, we now use SUMIFS, which can admit more than one criterion. 

For the same dataset, we want to add values from 6 to 9, including both 6 and 9. To do so, we use the following formula:

=SUMIFS(A1:E20, A1:E20, ">=6", A1:E20, "<=9")

The result is as follows:

SUMIF function adding all entries with values between 6 and 9.
SUMIF function adding all entries with values between 6 and 9.


To better understand how this happened, let us now the syntax of SUMIFS function for our example:

=SUMIFS(sum_range, criterion1_range, criterion1, criterion2_range, criterion 2)

sum_range = the range containing the values to sum that fit the criteria set

criterion1 = criterion 1 being applied to the range

criterion1_range = the range where criterion1 is applied

criterion2 = criterion 2 being applied to the range

criterion2_range = the range where criterion2 is applied

In our example, we are applying the criteria to the single range, the sum_range, criterion1_range, and criterion2_range because they refer to the same range of values.

Adding the corresponding values of a certain entry

If you want to add values that have a certain tag, like a name, set, or date, then the SUMIF and SUMIFS function can be easily tweaked to do so.

For this section, we consider a sheet with two columns: Column A containing the set where the values at Column B belong.

The range containing the values and the set to which it is apart of.
The range containing the values and the set to which it is apart of.


Let’s say we want to add all entries that belong to set A. To do so, we will use the following formula:

=sumif(A2:A101, "A", B2:B101)

We get the following result:

SUMIF function adding all entries that belong to set A.
SUMIF function adding all entries that belong to set A.


What happened? Here is the syntax of the formula that we used:

=SUMIF(range, “criterion”, sum_range)

In this case, we specified the range and the sum_range differently. The range is the range where the criterion is applied while the sum_range is the range where the values to be added are included. For our example, this works with the ranges stored in columns.

Adding all the entries except for those with certain value

It is possible to use SUMIF to add all entries that do not equal a certain value. To do so, use the symbol <> to indicate exclusion of the specified value. For our example, we want to exclude entries with a value equal to 10. The formula will look like the following:

=sumif(A1:E20, "<>10")

The result is as follows:

SUMIF function adding all entries with values not equal to 10.
SUMIF function adding all entries with values not equal to 10.


Adding entries, excluding entries with errors

If the values of entries are calculated using a formula or are imported from another source, it is possible to get errors. To add entries without errors, you can simply modify our formula in our previous section, including the exact string displaying the error.

If the error is #N/A, then the formula is

=sumif(A1:E20, "<>#N/A")

If the error is #ERROR then the formula is 

=sumif(A1:E20, "<>#ERROR")

Adding the entries that have a certain wildcard

A wildcard is marked by a string followed by an asterisk *. This tells Google Sheets to add entries whose corresponding criterion range begins in the string of characters preceding the asterisk.

For our example, we have 40 entries that belong to the groups Alpha, Beta, Gamma, and Delta:

A set of values tagged to their corresponding sets.
A set of values tagged to their corresponding sets.

Let’s say we want to add all the entries that are part of the set Alpha. We can opt to write the formula in this way:

=sumif(A2:A41, "Alpha", B2:B41)

But the following works as well:

=sumif(A2:A41, "Alp*", B2:B41)

The results are as follows:

SUMIF function adding all entries that belong to the set that starts with characters Alp.
SUMIF function adding all entries that belong to the set that starts with characters Alp.

Can I type the criterion in a cell and reference it in the formula?

Yes! It is possible to type the criterion to a cell and reference it in the SUMIF or SUMIFS formula. Using our last example, we can rewrite the formula as follows:

=sumif(A2:A41, D1, B2:B41)

And the result is the same:

SUMIF function adding all entries that belong to the set typed in another cell.
SUMIF function adding all entries that belong to the set typed in another cell.


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.