How To Use SUMIF in Google Sheets

SUMIF Formula in Google Sheets

The SUMIF formula lets you to add together values in a specific range based on multiple criteria that you define.

It sounds boring but I promise you it isn’t.

Okay, it’s a bit boring. But it lets you quickly add up the total search volume of your carefully planned out topic clusters.

Read on to find out how to wrangle the SUMIF formula to speed up your marketing tasks.

What is the SUMIF Formula?

The SUMIF function is used to sum the numbers in a range based on a condition.

Similarly to the COUNTIF function, the SUMIF is a combination of two simpler functions, SUM and IF. If a range of cells meets the condition, then this function sums the numbers related to that condition.

Its syntax is as follows:

=SUMIF(range, criterion, [sum_range])

Where the variables mean the following attributes:

• range (required) is the range of cells that should be evaluated by the criterion.
• criterion (required) is the condition to be met. It can contain a number, a text, a date, a logical expression, a cell reference or any function.
• sum_range (optional) is the range in which to sum numbers. If you don’t specify it, then the range is summed.

When to use SUMIF for marketing stuff?

• Adding up traffic based on specific analytics criteria.
• Everyday data manipulation and sorting.
• Summing up keywords that match specific criteria.
• Getting quick overview of topic clusters (explained more below)

How To Use The SUMIF Formula in Google Sheets

As an example, let’s see a simple list that contains your expenses charged in different currencies. With the help of the SUMIF function, you can sum the total expenses in each of the currencies.

The way it works is quite straightforward: SUM the numbers from the Price column IF the value in the Currency column is EUR.

We define the following arguments:

• range is the range of currencies in which we evaluate your condition, which is the range of B2:B9.
• criterion is a textual condition here, the name of the currency, so either “EUR”, “GBP” or “USD”.
• sum_range contains the range of numbers in which to sum the values that meet the criterion. In this example, it’s the range of prices, so A2:A9.

Putting the arguments together, we get the following formulas for the three currencies:

=SUMIF(B2:B9, "EUR", A2:A9)

=SUMIF(B2:B9, "GBP", A2:A9)

=SUMIF(B2:B9, "USD", A2:A9)

It’s important that the two ranges (range and sum_range) must be of equal size and shape, otherwise, you get an error.

Another way to use the SUMIF function is without a sum_range. In this case, you evaluate the condition and sum the numbers from the same range.

Of course, it only works on numeric ranges. If you want to sum the numbers that meet a certain criterion that you specify in your SUMIF function.

More SUMIF Formula Examples

You can use comparison operators, such as greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=) or not equal to (<>) in your criterion.

For example, suppose you want to sum all the product prices that are greater than or equal to 1000. In this case, you specify the condition in the same range where you have your numbers to sum.

The function looks like this:

=SUMIF(A2:A9, “>=1000”)

The criterion expression must be enclosed in double quotation marks in the SUMIF function, such as “>=1000”.

Here the number 1000 was provided manually, but you may use cell references as well. If your criterion has an expression and a cell reference, then you need to join them together by a ‘&’ character, for example “>=”&B12.

Here the number 2000 is supplied from the cell B12. You can change this number in the cell to directly modify the condition of the SUMIF function.

Getting an Overview of Topic Clusters with SUMIF…

Hands up who’s spent too long inside a spreadsheet planning out content marketing? Here’s a really great way of getting a quick overview of topic clusters using the SUMIF formula.

Say you have a spreadsheet with the following column headers:

• Topic cluster
• Keyword
• Volume

So this would be your very basic keyword research sheet template where you’ve grouped your keywords under a topic.

Now let’s say you want to add up all the monthly searches each topic cluster gets.

It’s pretty easy with this formula:

=SUMIF(B3:B11,"SEO",D3:D11)

Here’s how it works:

• The first part of the formula should be the cell range where your topic clusters/categories are in.
• Next should by the cluster name that you want to look up and sum
• Finally, you add the cell range with thedata you want to add (search volume in this example but it can be any value)

Summary

IF you liked this tutorial (see what I did there) you might also like these:

• How To Use COUNTIF
• How To Use IF / IFERROR