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

(1*E3)+(0*E4)+(0*E5)+(0*E6)+(1*E7)+(0*E8)+(0*E9)+(0*E10).

This gives you the answer of 1628 because this next formula equals 1628.

(1*751)+(0*483)+(0*789)+(0*932)+(1*877)+(0*162)+(0*258)+(0*517)

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.