How to Use SUMIF function in Google Sheets? Examples!

Summing up a range of numbers may be quite easy for even a beginner to Google Sheets.

However, when it comes to summing up numbers based on a condition, that’s when things start to get tricky.

Well not really.

Google Sheets provides a convenient function called SUMIF to help with conditional sums.

Once you understand the basic syntax and look at a few examples, you’ll want to use the SUMIF function every chance you get.

In this tutorial, we will help you understand the basic syntax for the Google Sheets SUMIF function and take you through some use-cases to help you see for yourself how it can be applied.

What does the SUMIF Function Do?

The SUMIF function is basically just an amalgamation of the SUM and IF functions. Its application is simple.

The function simply scans through a range of cells to find those that match a given condition.

When a match is found, the number corresponding to the cell is included in a group of selected numbers.

Once the function is done scanning through all the cells in the range, it takes the group of selected numbers and sums them up!

Syntax of the SUMIF Function in Google Sheets

The syntax of the SUMIF function is as follows:

=SUMIF(range, condition,[sum_range])

Here,

  • range is the group of cells that are tested for a condition.
  • condition is the criteria that a cell in range needs to fulfill to qualify as a match. The condition can be a value (number, text, date) or a reference to a cell containing the criteria.
  • sum_range is an optional parameter. If included, it is the range of cells containing values that will be added if its corresponding number in range matches the condition. If the sum_range parameter is not included then it is assumed that the range in the first parameter is also the sum_range.

As you can see from the above syntax, there are two ways to use the SUMIF function

  • without a separate sum_range
  • with a separate sum_range.

If all three parameters are given, then the SUMIF function checks each cell in the range to see if it matches the condition.

If a cell does match the condition, then the SUMIF function takes the corresponding cell value in sum_range and includes it in the final sum.

If just the first two parameters are given, then the SUMIF function goes through each cell in the range and adds only those cells that match the condition.

It finally returns the sum of all cells in the range that match the criteria.

Use Cases for the SUMIF Function (Examples)

The SUMIF function is such a versatile function that it can be used in a number of ways to accomplish a wide variety of tasks.

For example, you can use it to:

  • Find the sum of only positive or negative numbers in a range
  • Find the total sales for a department
  • Find the total expenditure before a given date

These applications just scratch the surface. There are a number of other possibilities and areas where the SUMIF function can come in very handy.

However, for this article, let us just take a look at these three use-cases. We will include another special use-case when we explain wildcards.

Using SUMIF with Number Condition

The first use-case is to find the sum of only positive or negative numbers in a range.

Let us look at a dataset that consists of a combination of positive and negative integers, as shown below.

SUMIF Dataset sum of positive numbers

We want to add up only the positive numbers in the range A2:A10. Here’s how the SUMIF function can be applied in this case:

  1. Select the cell where you want the result of the sum to appear ( C2 in our case ).
  2. Type the following formula in the cell:
    =SUMIF(A2:A10,”>=0”)

    Notice that we did not include the third parameter in this case

  3. Press the return key

This should display the sum of positive numbers in cell C2.

SUMIF formula to add positive numbers only

Explanation of the Formula

In this example, the SUMIF function checked each cell from A2 to A10 and selected only those cells that have values greater than or equal to 0.

It then added up all the selected values and displayed the result in cell C2.

SUMIF formula explained for positive numbers

Note: If you want to display the sum of all negative numbers instead, all you need to do is change the condition to “<0”.

Using SUMIF with Text Condition

Let us take a look at another example.

Below we have a dataset where we want to sum up sales figures for the packaging materials department.

Using SUMIF with Text Condition dataset

Below is how you can do this using SUMIF formula:

  1. Select the cell where you want the result of the sum to appear ( D2 in our case ).
  2. Type the following formula in the cell:
    =SUMIF(A2:A10,”Packaging”,B2:B10)
  3. Press the return key

This should display the total sales of the Packaging department in cell D2.

SUMIF formula to sum based on text

Explanation of the Formula

In this example, the SUMIF function checked each cell from A2 to A10 and looked for only those cells that contain the value “Packaging”.

For each cell that contains the word “Packaging”, the SUMIF function selected its corresponding sales value in column B. It then added up all the selected values and displayed the result in cell D2.

SUMIF formula to sum based on text explained

Note: If you want to display the total sales for any other department, you can simply replace the condition in the second parameter to the department name you need. Don’t forget to enclose the department name in double-quotes.

Using SUMIF with Date Condition

Now, let us look at an example where we want to find the total expenditure before a given date, say 1 September, 2019.

We will use the data set shown below to demonstrate how to use the SUMIF function for this problem.

SUMIF with date condition dataset

  1. Select the cell where you want the result of the total expenditure to appear ( D2 in our case ).
  2. Type the following formula in the cell:
    =SUMIF(A2:A10,”<”&DATE(2019,9,1),B2:B10).
  3. Press the return key

This should display the total expenditure before DATE(2019,9,2), which just means 1 September 2019. We only used the DATE function to represent the date.

Note: We must concatenate the operator (“<”) to the date using an ampersand (&).

Explanation of the Formula

In this example, the SUMIF function checked each cell from A2 to A10 and looked for only those cells that contain dates before 1 September 2019.

For each matching cell, the SUMIF function selected its corresponding expenditure value from column B.

It then added up all the selected expenditure values and displayed the result in cell D2.

SUMIF with date formula ex[lained

There are a number of other ways in which you can use the SUMIF function.

Whenever you need to find the sum of values based on a condition, you can use this function, just by getting creative with the ‘condition’ part of the formula.

Using SUMIF with WildCards

One creative way of using the SUMIF function is by incorporating wildcards into the condition part of the function.

For example, say you have the following dataset containing quantities of different mobile phones in stock.

SUMIF with wildcard datasets

If you want to find the total quantity of all Samsung models, you can use wildcards like ‘*’ or ‘?’ in your SUMIF function as follows:

  1. Select the cell where you want the result of the total sales to appear (D2 in our case).
  2. Type the following formula in the cell:
    =SUMIF(A2:A10, “Samsung*”,B2:B10)
  3. Press the return key

This should display the quantity of Samsung phones in cell D2.

SUMIF formula with wildcard datasets

Note: The asterisk wildcard (*) is usually combined with a word or letter stem to find different variations of the term.

Explanation of the Formula

In this example, the condition “Samsung*” means ‘find all cells that contain the word, Samsung’.

It doesn’t have to be an exact match, but the cell should contain the word ‘Samsung’, along with any other character(s).

Once a match is found, the SUMIF function takes the qty value corresponding to the matching cell and adds it to the list of selected quantity values.

Once it completes going through all the models, the SUMIF function sums up the selected quantity values and displays the result in cell C2.

SUMIF formula with wildcard explained

You can use the ‘?’ wildcard in the same way. The ‘?’ wildcard is used to represent a single character, anywhere in the text string.

So if you wanted to search for, say, all Apple iPhone X models, you can use “Apple iPhone X?” in the condition.

Points to Remember

In using the SUMIF formula, there are a few important points that you should keep in mind, so you can apply it to your data more effectively.

  • If using a separate sum_range, remember to make sure that both the range and sum_range have an equal number of cells.
  • The SUMIF function is not case-sensitive.
  • If the condition contains a text value, date, or wildcards, then it must be enclosed in double-quotes.
  • Cell references in the condition are not enclosed in quotes.
  • If the condition consists of a combination of an operator and a cell reference or another function (for example, the DATE function), then they must be joined together by an ampersand (&). For example,
    • “<”&DATE(2019,9.1)
    • “>”&B2
  • You can only use one condition in the SUMIF function. If you want to use more than one condition then you will need to use the SUMIFS function instead.
  • You cannot substitute the range and sum_range references with arrays.

In this tutorial, we showed you how to use the SUMIF function in Google Sheets. This is a very versatile function that has a wide variety of applications.

We showed you how you can use the function in different use-cases and we encourage you to apply the SUMIF function whenever you need to sum up numbers based on a condition.

Other Google Sheets tutorials you may like:

Leave a Comment