“Export to Excel is the 3rd most common button in BI apps…after OK and Cancel.”
—Rob Collie, one of the founding engineers behind PowerPivot at Microsoft, and now the leader of PowerPivotPro.com.
Whether Rob’s running joke is true or not — and he says that it likely is true — it illustrates a continual challenge: What’s the best way for Excel users to organize and report business data in Excel?
My article, Introducing Excel-Friendly Databases, explained four general ways to store data as an Excel database. And in How to Set Up a Pivot Table as an Excel-Friendly Database I showed how to do it with PivotTables.
Now it’s time to take the next step: Returning the data from Excel Tables and other Excel databases into your reports and analyses.
You’ll use two key functions for returning values from your Excel Tables and other databases: SUMIFS and SUMPRODUCT.
The SUMIFS Worksheet Function
Excel has had the SUMIF function for a long time. But SUMIF had a significant problem: It could return a SUM based on only one criterion. So in Excel 2007, Microsoft introduced the SUMIFS function, which can use any number of criteria.
The functions arrange their arguments differently:
- =SUMIF(criteria_range, criteria, sum_range)
- =SUMIFS(sum_range, criteria_range,criteria,…)
The SUMIFS function can have as many sets of criteria_ranges and criteria that you want. (There might be an upper limit, but if there is, I doubt you’ll ever reach it.)
To save confusion, I recommend that you stop using the SUMIF function entirely, even if you need to use only one criterion. That way, you’ll get used to using SUMIFS and you’ll always know the proper sequence of the arguments in your function.
The SUMPRODUCT Worksheet Function
Officially, the SUMPRODUCT function has these arguments:
=SUMPRODUCT(array1, array2, array3,…)
However, you’ll probably find it easier to use it like this:
=SUMPRODUCT(array1*array2*array3)
For example, if you want to find the total value of all black hats in your data, you could have a formula like this:
=SUMPRODUCT(Amounts*(Products=”Hats”)*(Colors=”Black”))
(Note: If you copy formulas with quotes from this blog post to your worksheet, you’ll get an error because HTML typically uses a different character for its quotes than Excel does. So in Excel, you’ll need to replace the quotes in your formula with the standard quote character.)
In the formula, Amounts is the column of values in your table of products and product colors, and Products is the column of products, which includes Hats. The next part of the formula, (Products=”Hats”), first resolves as a column of TRUEs, where the product does equal hats in certain cells, and FALSEs, where the product doesn’t equal hats in other cells.
Then, when you multiply that second array by the column of Amounts, the TRUE and FALSE values work like they were a column of 1 (one) and 0 (zero) values. So the result of the multiplication is that you have an array of values where the product equals hats, and zeros where the product doesn’t equal hats.
Next, when you multiply by the array of (Colors=”Black”), the only non-zero value in the final array is where an original value in the Amounts column was non-zero, and the Product is a hat, and the Color is black.
Finally, the SUMPRODUCT function adds that result array, giving you the total values for all black hats.
SUMIFS vs SUMPRODUCT, Which Should You Use?
If both functions can be used in a formula, I recommend that you use SUMIFS, because it calculates more quickly and uses less memory.
But there’s another reason for using SUMIFS: It accepts wildcard characters in its criteria values. For example, a criteria of “h*” accepts all items that begin with the letter “h”. (SUMIFS ignores case in its criteria.)
However, SUMPRODUCT offers a significant benefit: It can use calculations.
For example, suppose you have a table of sales by date, and you want to find the average sales for Wednesdays. You couldn’t use SUMIFS or AVERAGEIFS for this calculation, because your table doesn’t have a column that specifies the day of the week. But you could use SUMPRODUCT like this:
=SUMPRODUCT(Amount*(WEEKDAY(DateTime)=4))
/SUMPRODUCT((WEEKDAY(DateTime)=4)*1)
(Although I’ve wrapped this formula in two lines, you actually would enter it in one long line.)
The first line of the formula returns the total for all sales on a Wednesday.
The second line returns the number of Wednesdays found. It does so by generating a column of TRUE and FALSE values, which are converted into ones and zeros when we multiply by 1. (Adding zero would have done the same thing.) Then the second line finds the number Wednesdays by adding up all the ones in the column.
Finally, the formula divides the first result (the total of Wednesday sales) by the second (the number Wednesdays with sales) to produce the average value of sales for Wednesdays.
Note that there are certain functions you can’t use within a SUMPRODUCT function, but you can use most of them.
SUMIFS and SUMPRODUCT Examples
Let’s return data from this Excel Table, named Sales.
For the first few examples, I’ve set up four cells, named Product, Color, StartDate, and EndDate.
Also, as above, I’ll wrap most of the formulas to several lines so they’ll fit on the page. But you’ll enter them in one line, of course.
Suppose the Product cell contains the text “Ties” and the Color cell contains “Black”. Both of these formulas will return 9, the total for all Black Ties:
=SUMIFS(Sales[Amts], Sales[Products],Product, Sales[Colors],Color)
=SUMPRODUCT(Sales[Amts]*(Sales[Products]=Product)*(Sales[Colors]=Color))
Suppose the StartDate cell contains the date 2012-01-01 and the EndDate cell contains 2012-02-01. Both of these formulas will return 24, the total of all sales in January:
=SUMIFS(Sales[Amts], Sales[DateTimes],”>=”&StartDate, Sales[DateTimes],”<”&EndDate)
=SUMPRODUCT(Sales[Amts]*(Sales[DateTimes]>=StartDate)*
(Sales[DateTimes]<EndDate))
Now let’s combine these sets of formulas to return the total of all Black ties sold in January:
=SUMIFS(Sales[Amts], Sales[Products],Product, Sales[Colors],Color, Sales[DateTimes],”>=”&StartDate, Sales[DateTimes],”<”&EndDate)
=SUMPRODUCT(Sales[Amts]*(Sales[Products]=Product)*
(Sales[Colors]=Color)*(Sales[DateTimes]>=StartDate)*
(Sales[DateTimes]<EndDate))
(I’ve wrapped these formulas again, but you actually would enter each in one long line.)
Suppose, for some reason, we want the total of all products whose names end in “ts”.
With SUMIFS, we could use wildcards. Here, the cell named Product2 contains the text “*ts” (that is, we have an asterisk, *, followed by “ts”):
=SUMIFS(Sales[Amts], Sales[Products],Product2)
With SUMPRODUCT, we can use string functions to return the same result. So assume that the cell named Product3 contains the text “ts” (that is, we have NO asterisk, just “ts”):
=SUMPRODUCT(Sales[Amts]*(RIGHT(Sales[Products],2)=Product3))
Or, if we want to use wildcards, we can use:
=SUMPRODUCT(Sales[Amts]*(NOT(ISERROR(SEARCH(Product2,Sales[Products],2)))))
Excel’s SEARCH function can use wildcards. But it returns an error value when nothing is found. So we use NOT(ISERROR(SEARCH([whatever]))) to return TRUE if our search string is found and FALSE otherwise.
You can use SUMIFS and SUMPRODUCT to return many types of results from Excel Tables. But if you’ve not used them before, perhaps this will get you started.
