Some unlikely uses for Excel's SUMPRODUCT function

exceltips.jpg

Spreadsheets and graphs on a desk
xfgiro/istock
13

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.

avatar
By Geosherman
Jun 26th 2015 01:10

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

Thanks (0)
avatar
By JoyM
Jun 26th 2015 01:10

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

Thanks (0)
avatar
By Jeyaprakash
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By JPDesmond
Jun 26th 2015 01:11

Terrifically helpful explanation. Thank you.

Thanks (0)
avatar
By Taylor
Jun 26th 2015 01:11

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)
avatar
By NicholasjHassan
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By CRave
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By Guest
Jun 26th 2015 01:11

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)
avatar
By Madhukar KR
Jun 26th 2015 01:11

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)
avatar
By safeer
Jun 26th 2015 01:11

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

Thanks (0)
avatar
By fanzheng
Jun 26th 2015 01:11

Figure 4 really helped me out. thanks a lot!

Thanks (0)
avatar
By M
Jun 26th 2015 01:12

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

Thanks (0)
avatar
Jul 6th 2016 17:51

Crystal clear in explaining...learned a new way!

Thanks (0)