The SUMPRODUCT function has been my favourite function for about 20 years. It is so flexible. Soon it will be redundant thanks to dynamic arrays.
In the old days (pre Excel 2007), SUMPRODUCT was the easiest way to perform multi-condition SUMIF calculations. When SUMIFS was released in Excel 2007 SUMPRODUCT become less important.
SUMPRODUCT still allows you to perform magical calculations like counting the number of Sundays between two dates – see this blog post.
But with the release of dynamic arrays earlier this year SUMPRODUCT is no longer required if you have the latest Excel version.
It will remain useful when dealing with users who are on older versions but over the coming years the SUMPRODUCT function will become a distant and fond memory.
Normal SUMPRODUCT
Let’s take the straightforward example that SUMPRODUCT was designed for – multiplying two ranges together and adding up the results. See image below.
SUMPRODUCT literally means to SUM the results of PRODUCTs. (To get the product of two numbers you multiply them together.)
We need to multiply the Value1 values by the Value2 values and add up the results of the three multiplications. This can be done via 4 separate calculations C2:C5.
We can also calculate the total using a single SUMPRODUCT function in cell C6.
=SUMPRODUCT(A2:A4,B2:B4)
That formula works in all Excel versions.
With dynamic arrays we can use the SUM function to do the same calculation in cell C7.
=SUM(A2:A4*B2:B4)
By placing the * between the two ranges we are re-creating the SUMPRODUCT function. The multiplications are done and the totals are added up.
Special SUMPRODUCT
One of the remaining advantages of SUMPRODUCT is the ability to use other functions between the brackets that allow those functions to handle ranges instead of cells. See Image below.
The LEN function returns the number of characters in a cell.
The formula in cell E2 extracts the individual LEN results and adds them all up.
=SUMPRODUCT(LEN(A2:A10))
The SUM function in cell E3 does the exact same thing.
=SUM(LEN(A2:A10))
Game over for SUMPRODUCT – I will be sorry to see you go.
Please note: I reserve the right to delete comments that are offensive or off-topic.