In an earlier post I demonstrated How to sum cells with multiple criteria in Excel 2003. I used an Array Formula consisting of the SUM function and the IF function.
In Excel 2007 however a new function is introduced that facilitates sum with multiple criteria: the SUMIFS function.
Syntax
SUMIFS (sum_range, criteria_range1, criteria1, criteria range2, criteria2…) … up to 127 range/criteria can be specified.
sum_range : Required. One or more cells to sum.
criteria_range1: Required. The first range in which to evaluate the associated criteria.
criteria1: Required. The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added.
criteria_range2, criteria2, … : Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.
In the example below we want to sum the sales for “John” in quarter 2 (Q2). I.e. we have two criteria:
1. “John” in column B.
2. “Q2″ in column C.
We will use the following formula for this purpose:
=SUMIFS(D2:D17;B2:B17;”John”;C2:C17;”Q2″)
Only rows 3, 10 and 14 will qualify for this formula (yellow color) and it will give us a result of 9547 as you can see.
Related posts:
How would you use the SUMIFS with multiple OR conditions.
EG: Sum if quarter = Quarter1 AND if (Product = Product1 OR Product2)
This is one approach:
1. Create a new column (you can make it a hidden column if you don’t want it to show in your report). Let us assume its column E.
2. Enter the following formula in E2, then autofill to the end of the range:
=OR(A2=”product1″;A2=”Product2″)
3. This formula will generate a TRUE value if the product is either Product1 or Product2.
4. Modify the SUMIFS formula to add a new criteria as follows:
5. =SUMIFS(D2:D17;B2:B17;”John”;C2:C17;”Q2″;E2:E17;TRUE)