Horizontal sumproduct

[benwork]

Hi

Im trying to do a horizontal sumproduct.

This is what Ive got so far, but I know it wont work. Ive been trying to play around with the transpose Ive been reading about but I cant get it working. Could anyone point me in the right direction?

=SUMPRODUCT(BP3:BQ3,BK3:BL3,BF3:BG3,BA3:BB3,AV3:AW3,AQ3:AR3,AL3:AM3,AG3:AH3,AB3:AC3,W3:X3,R3:S3,M3:N3,H3:I3,C3:D3)

Thanks in advanced

 

[barry houdini]

As long as all ranges are horizontal and the same size (which appears to be the case with your formula) then there's no reason why that won't work - it works for me as expected.

What results do you get?

Blanks will be treated as zeroes, though, so if any of those 2 cell ranges are empty the result will be zero....

 

[Aladin Akyurek]

Hi

Im trying to do a horizontal sumproduct.

This is what Ive got so far, but I know it wont work. Ive been trying to play around with the transpose Ive been reading about but I cant get it working. Could anyone point me in the right direction?

=SUMPRODUCT(BP3:BQ3,BK3:BL3,BF3:BG3,BA3:BB3,AV3:AW3,AQ3:AR3,AL3:AM3,AG3:AH3,AB3:AC3,W3:X3,R3:S3,M3:N3,H3:I3,C3:D3)

Thanks in advanced

This would yield a sum. Is this not what you are after?

 

[T. Valko]

Hi

Im trying to do a horizontal sumproduct.

This is what Ive got so far, but I know it wont work. Ive been trying to play around with the transpose Ive been reading about but I cant get it working. Could anyone point me in the right direction?

=SUMPRODUCT(BP3:BQ3,BK3:BL3,BF3:BG3,BA3:BB3,AV3:AW3,AQ3:AR3,AL3:AM3,AG3:AH3,AB3:AC3,W3:X3,R3:S3,M3:N3,H3:I3,C3:D3)

Thanks in advanced

Can you explain in words what you're wanting to accomplish with that formula?

 

[jim may]

Yeah, I'm curious.. I'm confused here with what I have produced? Can't seem to prove the 507,000.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1		20	30	40	50	60	70	80	90		2000
2											4200
3	507000										7200
4											13400
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A3	=SUMPRODUCT(D1:E1,F1:G1,H1:I1)
K1	=E1*D1
K2	=G1*F1
K3	=I1*H1
K4	=SUM(K1:K3)

 

[barry houdini]

Hello Jim - the result of

=SUMPRODUCT(D1:E1,F1:G1,H1:I1)

isn't =(D1*E1)+(F1*G1)+(H1*I1)

it's

=(D1*F1*H1)+(E1*G1*I1)

which in your example is

=(40*60*80)+(50*70*90)

=192000+315000 = 507000

 

[benwork]

Basically I want the formula to read something like this but I get a #value error

=(C3*D3)+(H3*I3)+(M3*N3)+(R3*S3)+(W3*X3)+(AB3*AC3)+(AG3*AH3)+(AL3*AM3)+(AQ3*AR3)+(AV3*AW3)+(BA3*BB3)+(BF3*BG3)+(BK3*BL3)+(BP3*BQ3)

with this forumla

=SUMPRODUCT(BP3:BQ3,BK3:BL3,BF3:BG3,BA3:BB3,AV3:AW3,AQ3:AR3,AL3:AM3,AG3:AH3,AB3:AC3,W3:X3,R3:S3,M3:N3,H3:I3,C3:D3)

I get a 0 value for everything

There are zeros for some columns, but it was always be in pairs. eg c3 and d3 might be zero but h3 and i3 have data

I hope this makes sense. Its 1.20am lack of sleep is taking its toll

 

[barry houdini]

As per my reply to Jim, SUMPRODUCT doesn't multiply the elements within the same range, which is what you want, it multiplies the first element of every range added to the product of the second element of every range, so your SUMPRODUCT will give you

=C3*H3*M3*R3 .....etc.

Your first formula should work OK, if you get #VALUE! error that means there's probably some text in at least one of the cells.....

 

[barry houdini]

to get the same result as.....

=(C3*D3)+(H3*I3)+(M3*N3)+(R3*S3)+(W3*X3)+(AB3*AC3)+(AG3*AH3)+(AL3*AM3)+(AQ3*AR3)+(AV3*AW3)+(BA3*BB3)+(BF3*BG3)+(BK3*BL3) +(BP3*BQ3)

....try this version....

=SUMPRODUCT(C3:BP3,D3:BQ3,(MOD(COLUMN(C3:BP3)-COLUMN(C3),5)=0)+0)

That will also ignore any text......but I suggest you find out what caused the #VALUE! for the first formula as the issue might affect the result.....

 

[Aladin Akyurek]

Basically I want the formula to read something like this but I get a #value error

=(C3*D3)+(H3*I3)+(M3*N3)+(R3*S3)+(W3*X3)+(AB3*AC3)+(AG3*AH3)+(AL3*AM3)+(AQ3*AR3)+(AV3*AW3)+(BA3*BB3)+(BF3*BG3)+(BK3*BL3)+(BP3*BQ3)

with this forumla

=SUMPRODUCT(BP3:BQ3,BK3:BL3,BF3:BG3,BA3:BB3,AV3:AW3,AQ3:AR3,AL3:AM3,AG3:AH3,AB3:AC3,W3:X3,R3:S3,M3:N3,H3:I3,C3:D3)

I get a 0 value for everything

There are zeros for some columns, but it was always be in pairs. eg c3 and d3 might be zero but h3 and i3 have data

I hope this makes sense. Its 1.20am lack of sleep is taking its toll

Try...

=SUMPRODUCT(--(MOD(COLUMN(C3:BP3)-COLUMN(C3),5)=0),C3:BP3,D3:BQ3)