Sumproduct combined with Sum

**Exceler** · 11-25-2010, 03:50 AM

I'm looking for the syntax to multiply a column of Rates (eg A3:A5)
by the sums of selected values (eg C3:D5) in the row for each Rate.

A B C D E
1 Hrs Hrs Hrs Hrs
2 Rate Jan Feb Mar Apr
3 100 10 40 70 75
4 200 20 50 80 85
5 300 30 60 90 95

The result would equal A3*sum(C3:D3)+A4*sum(C4:D4)+A5*sum(C5:D5)

I know this can be done by using separate sum and sumproduct formulas,
but I'm looking for a single formula, since I need to use it in many instances in a very large spreadsheet.

The following attempt, of course, does not work:

=SUMPRODUCT(A3:A5,SUM(C3:D3):SUM(C5:D5))

Any assistance will be greatly appreciated.

**DonkeyOte** · 11-25-2010, 03:58 AM

It's unclear how you intend to determine "selected values" but in terms of the specifics of the ex.

Please Login or Register  to view this content.

Originally Posted by Exceler

I'm looking for a single formula, since I need to use it in many instances in a very large spreadsheet

in which case you should be avoiding SUMPRODUCT (it's very inefficient) - you would be better creating a suitable Product calculation on each line and Summing accordingly.

**Exceler** · 11-25-2010, 04:09 AM

Thanks, that did it. I need to study up on the logic behind the syntax used in Excel functions.

**DonkeyOte** · 11-25-2010, 04:14 AM

The SUMPRODUCT link in my sig. outlines the function (and alternative syntax) in more detail.

To reiterate though - by the sounds of it you should be looking to avoid SUMPRODUCT
(less elegance in this instance is likely to lead to far better calculation performance overall)