Formula Power
How to Use SUMIFS with Criteria Lists, Part 1: Summarizing Sales
The SUMIFS function returns a sum for nearly any number of
single criteria. But here's how you can return the sum for many criteria
listed in a Criteria List.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

With one exception, SUMIFS is a very powerful function.
And it's very fast.
To understand the exception, suppose you have a table of
sales by product by date...as shown in Example 1, below. It's
easy to use a SUMIFS function to return the sales for one of
those products.
Here's the syntax for the SUMIFS function:
=SUMIFS(sum_range, criteria_range,criteria, ...)
And this formula, shown in the Example 1 figure below, returns
total sales for the product name entered in cell G7:
H7: =SUMIFS(Amt, Prod,G7)
That is, in this formula, SUMIFS returns the sum of all
values in the Amt column where any item in the Prod column
matches the single item in cell G7.
This also is true of other "S" functions like AVERAGEIFS,
COUNTIFS, MAXIFS, and MINIFS. We can use these functions to
summarize information about the Hats product or the Coats
product; but we can't use one function to summarize the of sales
for Hats and Coats...
...Or can we?
In the following examples I'll show you how to set up SUMIFS
and COUNTIFS functions so they can summarize your data from lists of criteria, and I'll give you some ideas
about how you might use this new capability.
Example 1: Return Data from a Sales Database
Here, we have a simple database of sales by month by
product.
What we want to do is to be able to enter a variable number
of product names in the Criteria List and see the total of all
sales for those products in the List Total cell.
That is, we want to use one SUMIFS formula to return the total for not
just one product—as shown in cell H7—but for any number of
products we want to add to our list.
Rather than telling you what to do, let me tell you how I
figured out how to do it...
I started with this formula:
H7: =SUMIFS(Amt, Prod,G7)
Here, of course, I knew that cell G7 is the criteria
argument for the first (and only) criteria range
for this formula. And Excel is expecting a single value for that
criteria.
But
I also knew that if we specify a range of cells for an argument
that asks for only one cell, and then we arrayenter the
formula, Excel will return an array of results, one for each
item in the list originally specified by cell G7, in this case.
So when I arrayentered...
H7: =SUMIFS(Amt, Prod,CriteriaList)
...that is, when I pressed Ctrl + Shift + Enter after I typed
in the formula, I got an
answer of 36...which was the same result I had in cell H7
originally. I was slightly confused at first, because it looked
like nothing much had happened. But then I clicked in the formula bar
and pressed the F9 key...
...where I saw this result:
={36;6;0}
Now everything made sense. Excel calculated the sum for each
item in the Criteria List, but returned only the first item in
the array...which was the value 36.
Therefore, because I wanted the sum of all those items, I
arrayentered this formula...
H7: =SUM(SUMIFS(Amt, Prod,CriteriaList))
...and got the value of 42, which is the value I was looking for.
However, working with array formulas is kind of a pain. So, I
wondered if I could set this up so that I wouldn't have to enter
my formula
as an array.
I knew that the SUMPRODUCT function treats its arguments as
an array, and you don't have to arrayenter it. So I entered
this formula normally:
H7:
=SUMPRODUCT(SUMIFS(Amt, Prod,CriteriaList))
And sure enough, I got 42 again.
Outstanding!
Then I moved the formulas around a little so that cell H7
held my original version again, and I put my final version
here:
H9:
=SUMPRODUCT(SUMIFS(Amt, Prod,CriteriaList))
But now, I wondered, what if I had thousands of items in my
table. How could I ensure that I had the correct result in cell
H9? That's why I set up the Test column.
Here's the formula for the first cell in the Test column...
E3:
=IFERROR(IF(MATCH(C3,CriteriaList,0),"x"),"")
This formula tells Excel to use an exact match (because its
third argument is zero) to search the
CriteriaList column for the value in cell C3. Because I didn't care
what the MATCH value was, I set it up just to return "x" if
MATCH returns any numeric value at all.
This formula uses two
shortcuts that I should explain...
First,
I didn't need to include the test "<>0" because the IF function
treats nonzero values as TRUE, and it treats ZERO values as
FALSE. In other words, this formula =IF(3,"x")
returns "x", because the IF function treats 3 as TRUE.
Second, I didn't need to include the third argument in the IF
function, because IF automatically returns FALSE if the first
argument isn't TRUE. That is, =IF(0,"x")
returns FALSE.
However, remember that MATCH returns #N/A—not zero—if no
value is found. And that means we'd never see the IF function's
FALSE value in any case. Instead, we'd get that an #N/A
value as an error. Therefore, I enclosed the
formula in an IFERROR function so it would return a null string
if the IF function returns an error value—the #N/A in this case.
By the way, I also could have used...
E3:
=IF(ISNA(MATCH(C3,CriteriaList,0)),"","x")
...which probably would have been easier to understand, but I
thought of the other method first.
In any case, after I entered the formula, I copied it down the Test
column, giving me the results shown in column E above. Then, to test my result, I entered this formula...
H10: =SUMIFS(Amt, Test,"x")
...which gave me the same result as in cell H9.
Example 2: Return Sales Totals with Dates
I'll
try to make this example fast.
Generally, when we have a table of data that includes dates,
we'll want to return subtotals for a range of dates.
So this example adds to the previous example the ability to
specify dates.
Because I was doing this in a hurry, and because I wanted to
keep the example figures as narrow as possible, I called the
first date Start and the second date Stop.
In real life, of course I would have used more descriptive
titles. But in any case, here's the formula that uses those date
values:
H10: =SUMPRODUCT(SUMIFS(Amt,
Prod,CriteriaList, Date,">="&Start,
Date,"<="&Stop))
Notice two things about this formula...
First, there are several spaces between "Amt," and "Prod",
and between each remaining pair of arguments after that. In
other words, I set up the SETIFS part of the formula something
like this:
=SUMIFS(sum_range, criteria_range,criteria
criteria_range,criteria criteria_range,criteria, ...)
When you add spaces between
arguments like that, Excel ignores the spaces. But I find them very
useful for visually separating each set of criteria range and
its criteria.
Second, notice that I use Date as the criteria range twice.
That's no problem at all. I tested it the first time to look for
all dates greater than or equal to the Start date, and the
second time to filter all dates that are less than or equal to
the Stop date. In combination, those two tests identify all
dates between the Start and Stop dates, inclusive.
Example 3: Return Sales by Region When Region Isn't Listed
In
this example, I show a table of sales by Office, which have the
unusual names of Smith, Jones, Doe, and Buck.
What we want to do is to summarize those sales by Region even
though the table doesn't include a Region column.
When I had to report summaries like this before I figured out
the formulas I'm showing you, I'd have to add a Region column to
my table, use INDEXMATCH to return the correct region for each
Office, and then write my SUMIFS formulas to filter on that new
Region column.
But when you use a list of criteria with your SUMIFS
formulas, that extra effort isn't needed. Here's the formula for
the cell shown:
G8: =SUMPRODUCT(SUMIFS(Amt,
Office,OfficeList)*(Region=F8))
Here, the SUMIFS array returns a total for each Office, no
matter which Region it's in. But then, the formula multiplies
those results by (Region=F8), which acts as an
array in this SUMPRODUCT formula, because Region also is a list.
Because cell F8 contains "North", the (Region=F8) array resolves to
{TRUE;FALSE;TRUE;FALSE} when it's used in the
formula in cell G8. And then, when the formula multiplies that
array of TRUE and FALSE values, the TRUE values are interpreted
as 1 and FALSE vales as zero.
As a consequence of this multiplication, cell G8 returns the
sum of only the North Region's values, because multiplying by
the FALSE values turns the other sums to zero.
Similarly, for the South Region, we have...
G9:
=SUMPRODUCT(SUMIFS(Amt,Office,OfficeList)*(Region=F9))
...and the formula returns the total of the SUMIFS results
only for the Offices belonging to the South region.
And finally, the total of those two results shown in cell G10
matches the simple calculation in this cell:
G12: =SUM(Amt)
Next week, in Part 2, I'll continue these examples by showing
you how to find totals in tables of incomestatement data, even
when the signs of the numbers normally would make that difficult to
do.
And finally, in Part 3, I'll show you a variety of short examples to
finish off this series. For example, if you have a table that
shows only sales by product, I'll show you how one formula can
return the gross profits for all those sales.
Also, by Part 3, I'll have a workbook ready for download with
examples from all three parts.
