Some unlikely uses for Excel's SUMPRODUCT function

exceltips.jpg

Spreadsheets and graphs on a desk
xfgiro/istock
12

While you may not be aware of Excel's SUMPRODUCT function, those in the know often use it for its stated purpose: multiplying two ranges together and then summing the total.

As shown in Figure 1, a single formula using SUMPRODUCT can replace the eight formulas listed in Cells D2 through D9. The SUMPRODUCT function multiplies the values in Column B by the values in Column C on a row-by-row basis, and then sums the total.

Although this is helpful, you might not be aware that SUMPRODUCT also can function like VLOOKUP or SUMIF on steroids.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

Replies

Please login or register to join the discussion.

thank you for the clear and concise explanation of this function.  

Thanks (0)

Finally a way to countif in another workbook that works!!!

Thanks (0)

Very simple and Clear way of Explanation....helped lot.......Thanx

Thanks (0)

Terrifically helpful explanation. Thank you.

Thanks (0)

This is awesome! Very clearly explained. Do you know anything about the relative calculation speed of using sumproduct vs. vlookup for single criteria formulas?

Another method I use to get this result with as many criteria as I need is to create another column and use a formula with "&" to create a unique string for what I want to pull. So using your example, I would add a formula in column F, starting in F2 and dragging down...=A2&D2. Then I would use sumif or countif referencing that column as my range...=sumif(F2:F19,C22&D22,D2:D19). You could extend the formula in column F to include city, chain, etc. by adding extra "&" (F2=A2&D2&B2&C2), if you want more criteria.

My method is much less elegant but it may be a little faster (I work with really large data sets so my workbooks can get very slow). Or it may not be any faster since I'm adding an entire extra column of formulas. Regardless, cool stuff...thanks!

Thanks (0)

David, I LOVE your tips! Great work David from Nick in Australia!

Thanks (0)

Thank you. This was eye-opening and very, very clear.

Thanks (0)

I think a SUMIFS or COUNTIFS function would be more helpful in these cases.

For Figure 6, you would use the formula:

=SUMIFS(E2:E:19,A2:A19,C22,D2:D19,D22)

For Figure 7, you would use the formula:

=COUNTIFS(A2:A19,C22,D2:D19,D22)

Thanks (0)

What if the critera to be considered is 3 factors - like if in the last table, if there was also a monthly break up of the sales of different commodities, what would be the resulting formula??

Say North GA - Apples - Jan = total sales

Have a similar situation where the extension of the 2 criteria into a 3 criteria is not working !!

Request support

Thanks (0)

You have given very informative result...way of explanation is pretty clear---safeer

Thanks (0)

Figure 4 really helped me out. thanks a lot!

Thanks (0)

All embedded pictures seem to be missing now. Can someone please fix? These are very helpful in explaining the formulas.

Thanks (0)