Skip to content

Explore the SUMIFS Function

In one of our previous articles, we showed you how to use the SUMIF function. In this article, we will go into how to use the SUMIFS function.

The SUMIFS Function

The SUMIFS function allows you to sum the cells in a spreadsheet based on several conditions.

For example, if you have a list of prices and would like to add up only the prices greater than $10 and less than $20, then you would use the SUMIFS function.

The SUMIFS function also allows you to sum the cells in a range based on criteria evaluated against other ranges of cells as well. To understand this, let’s look at how the SUMIFS function works.

How the SUMIFS Function Works

The SUMIFS function requires at least three arguments to work.

=SUMIFS(sum_range, criteria_range, criteria)

The sum_range is the range of cells added together. The criteria_range is the range of cells that is tested. Finally, the criteria is what the criteria_range is evaluated against.

The SUMIFS function will check to see which cells in the criteria_range meet the criteria. It will then add together the cells in the sum_range that are in corresponding positions to those cells in the first range which meet the criteria.

Take a look at this example.

The SUMIFS is looking for a ” North ” value in the first range.

The SUMIFS function then adds together the cells in the sum range that are in the same row (corresponding position) as those cells containing the value “North.”

As mentioned prior, the SUMIFS function allows you to sum the cells in a spreadsheet based on multiple conditions as well. It is not limited to testing only one criterion. For each additional criterion, there must be two more arguments – another criteria_range and another specified criteria.

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, etc.)

How to Use the SUMIFS Function

To help you better understand how to use the SUMIFS function, here are two visual examples.

Example 1

The first example illustrates how to use the SUMIFS function testing for two criteria.

=SUMIFS(B1:B12,B1:B12,">10",A1:A12,"North")

In the example below, the SUMIFS function is used to add together the cells containing a value greater than 10, but only if these cells correspond with the value of “North” in the adjacent range.

Example 2

The second example shows how to use the SUMIFS function testing for three criteria.

=SUMIFS(A1:A12,B1:B12,"Blue",C1:C12,"B",D1:D12,TRUE)

The SUMIFS function sums only the cells that correspond with cells containing the text value “Blue” in the first range, the letter “B” in the second range, and the value TRUE in the third range.

This Post Has One Comment

  1. This formula saved my Excel life! I’m always looking for ways to get rid of pivot tables and use formulas instead. This is the formula that I most use for that process! Don’t underestimate the power of SUMIFS!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top