Microsoft Excel Formulas: SUMIFS

The SUMIFS function adds all the arguments that meet certain criteria

Download the accompanying file(s) for this article.

SUMIFS is your go-to formula when SUMIF and VLOOKUP won’t work

The SUMIFS Syntax

The syntax may look confusing, by breaking it down it becomes easier to understand.

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

=SUMIFS is the function we want to use.

(sum_range, is the range of cells to add up.

Criteria_range1 is the range that is testing using criteria1.

Criteria1, is the criteria which defines the cells in criteria_range1 to add.

Criteria_range2, criteria2 are additional ranges and their criteria.

You can enter up to 127 range/criteria pairs.

SUMIFS in Action

In the accompanying workbook, you will find a list of 400 rows of sales data which needs to be summarised.

SUMIFS

To begin with, we will calculate the value of sales of Screws in the East region. The formula will appear as:

=SUMIFS(E3:E402,B3:B402,"Screws",C3:C402,"East")

Let’s break that down into manageable chunks:

=SUMIFS(E3:E402 is the range to add up i.e the value of the sales.

B3:B402, is criteria_range1 i.e the range to be testing using…

“Screws” …criteria1 which is screws (note that text is enclosed within “ “)

C3:C402 is criteria range 2.

“East” is criteria 2.

In simple terms, add up the value of all screws sold in the East region.

The answer is 49,095.

 

Limitations of this Method

This method is good for working out a simple calculation but it has its limitations, the largest being that it isn’t dynamic. What if you wanted to change the product or region being added? This is where control cells can help you. By linking the formula to a control cell you can quickly change the formula without having to manually edit it.

Have a look at ‘Dynamic’ tab in the workbook to gain an understanding how the below data tables have been created. Remember to follow the formula syntax and it will make sense very quickly.

SUMIFS

 

 

If you’ve found this article interesting and or useful please share it on social media using the handy share buttons

Subscribe to our mailing list to be the first to know when new articles are published

At Happy Duck we build bespoke business and financial management tools for small, medium and large companies that help them achieve their goals. This makes us happy. Find out more about us and about our services.

More in Formula

0 Comments

Leave a comment

Your e-mail address will not be published. Required fields are marked *