# Some unlikely uses for Excel's SUMPRODUCT function

xfgiro/istock

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.

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

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.

### Replies

By Geosherman
Jun 26th 2015 01:10

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

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

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

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

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

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

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

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

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

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

Thanks (0)
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 (1)
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)
By safeer
Jun 26th 2015 01:11

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

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

Figure 4 really helped me out. thanks a lot!

Thanks (0)
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)
Jul 6th 2016 17:51

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

Thanks (0)