How to Use SUMIF Google Sheets Function [3 Easy 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.

There’s a convenient feature called the SUMIF Google Sheets function 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 Google Sheets 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!

What Is the Difference Between SUMIF and SUMIFS?

Before we dive too deep into the SUMIF function, it’s important to distinguish the difference between SUMIF and SUMIFS.

SUMIF – is used when only one condition needs to be met for summing

SUMIFS – you need to use this if there’s more than one condition that you want to the formula to account for. Check out our detailed SUMIFS guide to learn more.

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.

Google Sheets SUMIF 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 SUMIF Formula in Google Sheets for This Example

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 a Google Sheet Conditional Sum With Date Conditions

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.

How to Use SUMIF In Google Sheets 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.

How to Use Google Sheets SUMIF in Not Blank and Blank Cells

To do this, you use the same formula we’ve already been working with but with operators to define whether a cell is blank or not.

To SUMIF the criteria range is blank, you should use two quotations without a space in between like this “”
A screenshot showing how to sumif using blank cells as a criteria

In the example above, we used the range C2:C6 which has blank spaces in it as the range, “” (blank) as the criteria, and B2:B6 as the sum_range.

If you wanted to SUMIF the cells are NOT empty, you can use the not equal to operator “<>” without any numbers or words attached, like so:

A screenshot showing how to use sumif to calculate based on non-blank cells

SUMIF in Google Sheets FAQ

How Does the SUMIF Function Work in Google Sheets?

The syntax of the SUMIF function is:

=SUMIF(range, condition,[sum_range])

you need to define the range that contains the condition first, then input the condition. Optionally, you can also add a third column of data that needs to be added together based on the condition and the first range.

How Do I SUMIF Multiple Columns In Google Sheets?

There is a trick to making this work. But the columns have to be side by side.

The trick is to repeat the range as many times as the number of columns you want to account for in the function and wrap it in {} brackets. Let’s take a look at an example:

An example of how to use SUMIF accross multiple columns

Here, we wanted to get the SUM of John’s sales for Jan and Feb. So, that needs to check in two colums.

As you can see in the formula we but the range in twice as {D2:D6,D2:D6} then “John” as the criteria and finally the sum_range as B2:C6. That means that both columns will be used. You can do this for as many adjacent columns as you’d like.

Can the SUMIF Function Be Case Sensitive?

No, unfortunately, not on its own at least. But, you could use an ARRAYFORMULA alongside a function that is case sensitive, like the FIND or EXACT functions.

Let’s take a look at an example:

Using the ARRAYFORMULA to make a case sensitive SUMIF calculation

In the above example, Product C and Product c are different items. If we used the a normal SUMIF function, it would give the wrong result as it would add the two together.

We used the FIND function as its case sensitive and can find the exact term in the array. That needs to be wrapped in an ARRAYFORMULA to define the data highlighted in purple.

How Do I Use SUMIF and Not Equal to?

You just have to use the not equal to operator “<>” and a number or phrase as the criteria in your formula. Let’s take another look at our example and say we don’t want to include John’s sales in the SUM. In this case, we would put “<>John” as the criteria like below.

Using the not equal to operator in the SUMIF function

 

Useful Tips for the SUMIF Function

When using SUMIF in Google Sheets, 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 Google Sheets function. 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:

Sumit

Sumit

Google Sheets and Microsoft Excel Expert.

2 thoughts on “How to Use SUMIF Google Sheets Function [3 Easy Examples]”

  1. Thank you so very much! This worked like a charm. Saved me hours of racking my brains to figure out what to do.

    Reply
  2. Hi,

    This is super super helpful, how do I sum the contents for a week range, for example, give me the total units for the week of Monday July 19 to Friday July 23?

    Thank you for your help!

    Reply

Leave a Comment