It’s wild to think that everything we do with computers comes down to manipulating its 0 and 1 states. So, it makes sense that using Boolean logic (that is, the manipulation of 0 and 1 or FALSE and TRUE states) can prove incredibly powerful in data manipulation.
Download the exercise file here.
Let’s take an example here. In cells A1:I49, we have a weather forecast in tabular format with address and attribute in the columns and the dates across the rows. We want to populate the “itinerary”-style table starting in cell L1.
We want to “look up” values here, so an obvious choice might be VLOOKUP().
However, consider that we are looking up two attributes at once — both the destination and the date, and that the values we want to look up (weather attributes) are on a different “axis” in the lookup table (They are store in one column rather than one row.). Those are some heady obstacles. Our runner-up, then, might be PivotTables simply to “reshape” the data. This would work just fine.
But, let’s “hack” a solution ourselves: by using SUMPRODUCT() for array multiplication.
But first, SUMJOKE()
The first thing I like to bring up when discussing SUMPRODUCT() is this knee-slapper from our friend Jordan Goldmeier:
Q: How does the #Excel developer style their hair? A: With SUMPRODUCT(). #mvpbuzz
— Jordan Goldmeier (@Option_Explicit) October 8, 2013
Now that we have that comedic relief out of the way, let’s continue with the hacking!
SUMPRODUCT()with conditional logic
Generally we use SUMPRODUCT() to multiply entire arrays together to, for example, calculate a weighted average.
We can also combine it with conditional logic essentially to “look up” a value by multiplying corresponding 1’s and 0’s together with our lookup value. Each cell in our table is essentially the value of a combination of three unique dimensions — weather quality (Max Temp, Min Temp, etc.), destination, and date.
Let’s “slice” our lookup table starting in cell N2:
=SUMPRODUCT(($A$2:$A$49=$L2)*($B$2:$B$49=N$1)*($C$1:$I$1=$M2))
You will see that each cell is flagged as a “1.” So far, so good! Essentially you just multiplied 1 * 1 * 1 for each cell. Boolean logic at work!
By modifying any of these attributes, we will get a 0 as a resulting value. It’s essentially like multiplying 1 * 1 * 0… which returns a 0.
From here, we’ll ’round out’ our formula by multiplying our three arguments together by a fourth — the weather data itself (cells $C$2:$I$49). Our formula in N2 then becomes
=SUMPRODUCT(($A$2:$A$49=$L2)*($B$2:$B$49=N$1)*($C$1:$I$1=$M2)*$C$2:$I$49)
In “pseudo-code,” it’s like our final output is 1 * 1 * 1 * the information we want.
Awesome! Now we have a tidy itinerary for a nice and dry cross-country trip that looks suspiciously like my own Summer of George 2016 road trip!
Leave a Reply