When I first heard about the SUMPRODUCT formula in Excel, I almost dismissed it as a useless formula used by only a few users. How often would someone need to multiply 2 or more sets of numbers and add the results?

To get to the story, we know that the formula is typed as:

=SUMPRODUCT((B3:B7)*(C3:C7))

This is the same as taking B3 and multiplying it by C3, followed by the next set, and so on. Then add the derived total. The answer is 41.

Then I discovered something really exciting about the formula! Unbeknownst to many Excel users, the formula can actually be modified to add numbers based on conditions, for example, if you have a data set and need to report Customer A’s (sales) volume.

The formula can be entered by matching the ranges to the conditions. For example, we can equate the range A3 to A7 to A12 (which is Customer A) and add the values ​​in columns B and C that correspond to the rows in Customer A. The total is 11, based on the sum of the values ​​in B3, C3, B6 and C6.

Therefore, the formula is modified as follows to achieve this result:
=SUMPRODUCT((A3:A7=A12)*B3:C7)

Question, why do we have to use this when we can also achieve the same result with the SUMIF formula? The reason is that unlike SUMIF, SUMPRODUCT can support more than one condition! This is magnificent! We can summarize the values ​​of the price column or the volume column if the condition is added to the formula like this:

=SUMPRODUCT((A3:A7=A12)*(B2:C2=B11)*B3:C7)

It will return 9, the total of 3 and 6 in C3 and C6 respectively.

The formula captivated me a lot during the creation of the Excel Calendar. I was using conditional formatting to highlight individual cells with the help of VLOOKUP. But when I start highlighting a range of cells using a start date and end date, VLOOKUP fails. That’s when I discovered the power of the SUMPRODUCT formula that could help me work with multiple ranges and determine if the date is within the range. It works perfectly because I can use one range to determine the open date and another range to determine the close date. Any dates that fall outside the ranges will not be highlighted.