# SUMPRODUCT vs. SUMIFS with structured references

#### abaker77

##### New Member
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.

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### jasonb75

##### Well-known Member
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
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
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
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.

Replies
6
Views
130
Replies
12
Views
296
Replies
3
Views
264
Replies
8
Views
208
Replies
2
Views
432

1,127,765
Messages
5,626,742
Members
416,201
Latest member
brianhf

### 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?

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