SUMPRODUCT vs. SUMIFS with structured references

abaker77

New Member
Joined
Oct 11, 2011
Messages
8
Office Version
  1. 365
Hi - does anyone know of a use for SUMPRODUCT that couldn't be more easily satisfied by SUMIFS with structured references from a table ? Perhaps there are occasions where you can't use a table or convert a range into a table but I can't think of any.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,992
Office Version
  1. 365
Platform
  1. Windows
The original purpose of sumproduct, multiplying the per row values in 2 or more columns with or without criteria then summing the product of each row.

Multiple criteria in more than 2 columns.

There might be more but that is all that comes to mind.
 

abaker77

New Member
Joined
Oct 11, 2011
Messages
8
Office Version
  1. 365
Thanks for your reply. I wonder if there's a real-life or business-type example of needing the original purpose of SUMPRODUCT.
If SUMIFS wasn't available, then yes, SUMPRODUCT would be tremendously useful. But otherwise ?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,992
Office Version
  1. 365
Platform
  1. Windows
I wonder if there's a real-life or business-type example of needing the original purpose of SUMPRODUCT.
I would personally have the product of each individual row then a single sum, with or without criteria. So for me the the answer would be no, but I have answered a small number of threads over the last year or so where it was required.
But otherwise ?
One point to consider it that complex criteria with sumifs are limited and the formulas can be convoluted when compared to sumproduct equivalents. There are times where I've suggested a Sumifs formula in favour of Sumproduct and wondered if it was the better choice or not.

The second point in my earlier reply. Multiple criteria in more than 2 columns.

Using a simple example, with OR type criteria, this formula would only be able to cope with 3 out of the possible 27 combinations of criteria.
Excel Formula:
=SUMPRODUCT(SUMIFS(Sum_range,Criteria_Range1,A1:A3,Criteria_Range2,B1:B3,Criteria_Range3,C1:C3))
With 2 sets of criteria you can work around this by transposing one of them, but even that SUMIFS can not do alone, it needs a bit of help. Ironically Sumproduct is its best friend here.

Another one that just came to mind also, assuming that multiple rows and multiple columns meet the criteria this wouldn't be possible with Sumifs.
Excel Formula:
=SUMPRODUCT((A2:A10="Row Criteria")*(B1:J1="Column Criteria"),B2:B10)

The above could likely be resolved with other alternatives, for example {SUM(IF( instead of Sumproduct, but both are effectively the same. Sumproduct is (apparently) more efficient than an equivalent SUM(IF formula, and the syntax is easier to follow. I have seen many threads where people struggle with SUM(IF because of parenthesis errors. Dynamic array functions could also be used as alternatives in some cases.

There are also the occasions where SUMIFS would need help from the volatile Offset function where Sumproduct would not. Summing multiple columns against single criteria columns (something that is asked more often than you would think).

Again these are just from personal experience, both on and off of the forum. There could be many other scenarios.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I wonder if there's a real-life or business-type example of needing the original purpose of SUMPRODUCT
Weighted averages spring to mind. I quite often use it for those.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,291
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top