# Microsoft Excel Formulas: SUMIFS

The SUMIFS function adds all the arguments that meet certain criteria

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.

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.

#### 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.

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.