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.
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 Multi-Criteria Lists
If one multi-criteria list is useful, would using several multi-criteria 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 more-restrictive 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 multi-criteria 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.
