Formula Power
Advanced SUMIFS
Calculations with Criteria Lists
In this final article of the
SUMIFS series, you'll learn
more reasons to use advanced multicriteria lists in marketing and
finance. And you'll learn other options for using
this powerful method.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

In
Part
1 of this series, you learned how to use criteria lists for
marketing data. In
Part
2, we looked at financial data. And now, in Part 3, I'll
show you more marketing and financial examples, and also show
you quick examples of other ways to use them.
You can
download the workbook
with all examples here.
Example 5: Financial Data
In
Example 3 I showed you how to summarize sales by region even
though your table doesn't have a Region column.
Here, we do the same thing, while also adjusting the signs of
the data as needed.
The table of data in this figure contains a VERY summarized
General Ledger Trial Balance by month. But there are two
problems with this data.
First, because the type of statement isn't included with the
data, it's not easy to summarize it by the type of statement.
And second, as in
Example 4, all accounts are positive if they have their
natural Debit or Credit sign, and they're negative otherwise. We
therefore need to multiply the data by the appropriate sign.
We do all of that in a pair of examples. Here's the first,
for Profit, which is the sum of all accounts in the Income
Statement:
G11: =SUMPRODUCT(SUMIFS(Amt,
Acct,Accts, Date,">="&Start,
Date,"<="&Stop)*(Stmt=H11)*Sign)
As in earlier examples, the SUMIFS function returns the sum of
data from the Amt column for accounts in the Accts column. But
then, it multiples by two values. First, it multiplies by the
test (Stmt=H11), where cell H11 has the text Inc in this
case. And second it multiplies by the Sign for each account. And
finally, the SUMPRODUCT function returns the sum of the sums
returned by those SUMIFS subtotals.
The formula for Equity is virtually identical:
G12: =SUMPRODUCT(SUMIFS(Amt,
Acct,Accts, Date,">="&Start,
Date,"<="&Stop)*(Stmt=H12)*Sign)
This time, however, the formula returns the sum of the
Balance Sheet data, as specified by cell H12.
Example 6: Gross Profit from Sales Data
Many
accounting systems can generate sales data by product and month.
But because few of them include the cost of each product, it's
not easy to use that data to track the gross profits that sales are contributing
to the company.
This example shows you how to do that.
Here, each product in the ProdList has an associated
percentage for its gross profit margin.
To calculate the Gross Profit for any product sold within a
range of dates, we use the same approach that we used for
switching the signs of our GL accounts.
In this case, however, we multiply by a Margin column rather
than a Sign column. Here's the Gross Profit formula:
G12: =SUMPRODUCT(SUMIFS(Amt,
Prod,CriteriaList, Date,">="&Start,
Date,"<="&Stop)*Margin)
Here, after the SUMIFS function finds total sales for each
product in the ProdList, we multiply by the Margin list. By
doing so, the first item in ProdList is multiplied by the first
margin in the Margin column, the second by the second, and so
on.
Example 7: Using Several MultiCriteria Lists
If
one multicriteria list is useful, would using several
multicriteria lists be useful as well? I'll let you decide.
However, in this final set of examples, I'll illustrate
the results you can get from using more than one criteria list.
Here's the data for the four examples that follow. As you can
see, it shows sales by region and product for one period of
time.
I excluded dates in this data because they're not relevant to
these examples
Example 7a
Here's
the formula for the Total cell:
Total:
=SUMPRODUCT(SUMIFS(Values, Products,ProdList,
Regions,RegList))
As you can see, adding a second criteria list creates a
morerestrictive set of criteria, which the SUMIFS function applies as it works its way
down the list.
In this example, for instance, it returns the sales only for the
North region's hats product plus the South region's the Ties
product.
Also notice that the criteria are NOT case sensitive.
Example
7b
Here, you can see that if you leave out an item from your
pair of items, Excel essentially ignores that row in your criteria
list.
That is, it returns the total for only North Hats from the table
above.
But let's be more precise. In Part 2, in the discussion of
the
Gross Profit formula, I showed you how to turn a list
reference into an array constant. If we do that in the Total
formula, here's our result:
Total:
=SUMPRODUCT(SUMIFS(Values, Products,{"Hats";0;0},
Regions,{"North";"South";0}))
As
you can see, if you leave an empty cell in a criteria list,
Excel converts that empty cell to zero, and searches for that
value.
This is good to know, because if you happen to have a zero
value in your table's criteria range, Excel will find a match.
To see what I mean, if I enter a Products value of zero for
one of the South products, the preceding formula will return 12
in this case, rather than zero.
After you download my workbook of examples, give it a try.
Example
7c
When you use the asterisk (*) wildcard as a criteria,
the SUMIFS function allows any value for that criteria. The
same is true if you use it in multicriteria lists.
Here, for example, the Total formula returned the sum of
North Hats for any item sold in the South.
Example
7d
Finally, if you use the same criteria twice in your list,
you'll get the twice the results that you're probably expecting.
Therefore, when you set up your criteria lists, it's always a
good idea to make sure you have no duplicates.
You can
download the workbook
with all examples here.
