SUMPRODUCT vs. SUMIFS with structured references

[abaker77]

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.

 

[jasonb75]

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]

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]

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.

=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.

=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]

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.