The SUMIFS function adds all the arguments that meet certain criteria
The SUMIFS Syntax
The syntax may look confusing, by breaking it down it becomes easier to understand.
=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:
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.