Microsoft Office Tutorials and References
In Depth Information
Excel Functions for Your Data Model
Figure 11-14: You can use the SUMPRODUCT function to filter data based on criteria.
To understand how this works, take a look at the formula in cell E12 shown in Figure 11-14. That
formula reads SUMPRODUCT((C3:C10=”North”)*(E3:E10)).
In Excel, TRUE evaluates to 1 and FALSE evaluates to 0. Every value in Column C that equals “North”
evaluates to TRUE or 1. Where the value is not “North”, it evaluates to FALSE or 0. The part of the
formula that reads (C3:C10=”North”) enumerates through each value in the range C3:C10, assigning a 1
or 0 to each value. Then internally, the SUMPRODUCT formula translates to
This gives you the answer of 1628 because this next formula equals 1628.
Applying SUMPRODUCT formulas to a data model
As always in Excel, you don’t have to hard-code the criteria in your formulas. Instead of explicitly
using “North” in the SUMPRODUCT formula, you can reference a cell that contains the filter value.
You can imagine that cell A3 contains the word “North”, in which case, you can use (C3:C10=A3)
instead of (C3:C10=”North”). This way, you can dynamically change your filter criteria, and your
formula keeps up.
Figure 11-15 demonstrates how you can use this concept to pull data into a staging table based on
multiple criteria. Note that each of the SUMPRODUCT formulas shown here references cells B3 and
C3 to filter on Account and Product Line. Again, you can add data validation drop-down lists to cells
B3 and C3, allowing you to easily change criteria.