Managing Excel Data
The Two Functions You MUST Know to Return Values from Excel Tables and Databases
Excel offers two powerful worksheet functions that can return
just the data you need from any type of worksheet database.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

“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 at 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’s Three Types of Spreadsheet Databases, explained
three general ways to store ExporttoExcel, CSV, PivotTable,
and other such data as an Excel database. And in How
to Set Up a Pivot Table as a Spreadsheet 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 (Products=”Hats”) part of the
formula 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 switch to 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 nonzero value in the final array is where an original
value in the Amounts column was nonzero, 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 the two functions can both be used in a formula, I recommend
that you use SUMIFS, primarily because it tends to calculate
more quickly.
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 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 20120101 and the
EndDate cell contains 20120201. 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.
Take Your Next Steps
If you're looking for additional help with
this topic, I can help you in three ways. To learn
more, see
Excel Training, Coaching, and Consulting.
