Array with Sum and multiply

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Wondering if there is a way to use an array formula for this?

I'm trying to multiply data for several months (data arranged in columns) by
values contained in one column. In the example below I want to multiply .5
by 100, 125 and 75, .7 by 100, 125 and 75, etc. Then take the sum of these
products. I was thinking {sum(b1:d6*a2:a6)} would work, but it returns an
error.

Multiplier Jan Feb Mar
0.5 100 125 75
0.7 100 125 75
0.9 100 125 75
0.4 100 125 75
0.3 100 125 75


Any help on this is greatly appreciated.

Thanks
 
Multipliers are in col A then
put this formula in Col E =SUM(A2*{100;125;75}) and drag it down
 
You can use either SUM or SUMPRODUCT. When you use SUM you need the equal
sign and DON'T type the Curly bracket. These must be enter by typying Shift-
Cntl- Enter
 
try this

=SUMPRODUCT((B2:D2)*A2+(B3:D3)*A3+(B4:D4)*A4+(B5:D5)*A5+(B6:D6)*A6)

Is this what you want?

Pls click Yes if this help

cheers
 
The function posted will work if the B1 is changed to B2. Smproduct works
much simplier than your example

=sumproduct(b1:d6*a2:a6)
 
Thanks! xlmate's formula did work, but I have many more rows than I
illustrated in my example, so Joel, your formula will work very nicely.

Thank you both for your help.
 
I get a #VALUE! error with the comma form of the formula that you posted (whereas the multiplication form I posted works fine for me).
 
Rick Rothstein said:
I get a #VALUE! error with the comma form of the formula that you posted
(whereas the multiplication form I posted works fine for me).
Click to expand...

My fault. I missed that the second spanned columns B to D.
 
Back
Top