Some unlikely uses for Excel's SUMPRODUCT function

Columnist
Share this content
12

By David H. Ringstrom, CPA

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.
 
Figure 1: SUMPRODUCT multiplies values together and sums the products, but can also do more.
 
Excel's VLOOKUP function allows you to look up data from a table based on criteria that you specify, and has four arguments:
  • lookup_value – This is the data that you want to look for in the first column of the table array.
  • table array – The table array is a range of two or more columns.
  • col_index_num – This argument allows you to specify the column within the table array for which you want to return data.
  • range_lookup – In this position you indicate FALSE if you're seeking an exact match for the lookup_value, or TRUE if you want an approximate match. For instance, you'd use FALSE to look up the price of an inventory item, or TRUE if you're determining which tax bracket an income level falls into.
 
Figure 2: VLOOKUP allows you to look up a single value based on a single criteria.
 
As shown in Figure 2, VLOOKUP returns $85,106 from Column E for the first instance of Apples that appears in Column D. The additional instances of Apples are ignored. If you need to add up multiple values based on specific criteria, then SUMIF is a better choice. This function has three arguments:
  • range – This is a column or row where you want to look for specified criteria.
  • criteria – This is the same as the lookup_value for VLOOKUP.
  • sum_range – This is a column or row from which you want to add up numbers whenever the criteria is found in the range.
 
Figure 3: SUMIF allows you to add up multiple items, but also is limited to a single criteria.
 
Figure 4: SUMPRODUCT can return the same results as SUMIF.
 
As shown in Figure 3, SUMIF returns $396,495. In this case, every time it found a match in Column D on the word Apples, it added up the corresponding value in Column E. Like VLOOKUP, you can only search based on a single criteria. Fortunately, SUMPRODUCT allows you to add up values based on multiple criteria.
 
However, let's first use SUMPRODUCT to match based on a single criteria, as shown in Figure 4. In this case it returns the same result as SUMIF because we only provided a single criteria.
 
Conversely, in Figure 5, SUMPRODUCT returns $272,584 because we specified that we only want sales for apples sold in North Georgia.
 
Figure 5: SUMPRODUCT also can return results based on multiple criteria.
 
As shown in Figure 5, the formula looks at Cells A2 through A19 for the words North GA, and at Cells D2 through D19 for the word Apples. When both criteria are met, SUMPRODUCT adds up the corresponding values from Cells E2 through E19.
 
Figure 6: SUMPRODUCT also can return the number of matches that meet criteria you specify.
 
Finally, as shown in Figure 6, if you only specify criteria and omit the range to sum, then SUMPRODUCT returns the number of items that match the criteria that you specify.
 
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.
 
Related articles:

Replies

Please login or register to join the discussion.

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

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

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

Terrifically helpful explanation. Thank you.

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!

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

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

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)

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

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

Figure 4 really helped me out. thanks a lot!

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