The total is 11, based on the sum of the values in B3, C3, B6 and C6. For example, we can equate range A3 to A7 to A12 (which is Client A) and sum up the values in column B and C that correspond to the rows in Client A. The formula can be entered by equating the ranges to conditions. Then I found out something really exciting about the formula! Unknown to many Excel users, the formula can actually be tweaked to sum up numbers based on condition(s), for instance, if you have a set of data and you are required to present the (sales) volume of Client A. This is the same as taking B3 and multiply it by C3, followed by next setĪnd so on. To get to the story, we know that formula is keyed in as: How often would anyone need to multiply 2 or more groups of numbers together and add up the results!? In “pseudo-code,” it’s like our final output is 1 * 1 * 1 * the information we want.When I first learned about the SUMPRODUCT formula in Excel, I almost dismissed it as a useless formula used by only few users. It’s essentially like multiplying 1 * 1 * 0… which returns a 0.įrom 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). Boolean logic at work!īy modifying any of these attributes, we will get a 0 as a resulting value. 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. 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. 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. Generally we use SUMPRODUCT() to multiply entire arrays together to, for example, calculate a weighted average. Now that we have that comedic relief out of the way, let’s continue with the hacking! SUMPRODUCT()with conditional logic Q: How does the #Excel developer style their hair? A: With SUMPRODUCT(). The first thing I like to bring up when discussing SUMPRODUCT() is this knee-slapper from our friend Jordan Goldmeier:
This would work just fine.īut, let’s “hack” a solution ourselves: by using SUMPRODUCT() for array multiplication. Our runner-up, then, might be PivotTables simply to “reshape” the data. 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.).
We want to “look up” values here, so an obvious choice might be VLOOKUP(). We want to populate the “itinerary”-style table starting in cell L1. 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. 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. It’s wild to think that everything we do with computers comes down to manipulating its 0 and 1 states.
0 kommentar(er)
