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

 

[Sanjeev1976]

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

The formula

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2859681 height=20 width=64 align=left>=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)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>
gives the result for the below equation

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=left>=(BP3*BK3*BF3*BA3*AV3*AQ3*AL3*AG3*AB3*W3*R3*M3*H3*C3)+(BQ3*BL3*BG3*BB3*AW3*AR3*AM3*AH3*AC3*X3*S3*N3*I3*D3)</TD></TR></TBODY></TABLE>

and so it is 0.

 

[jim may]

Barry, Aladin,

You guys are UNREAL in your understanding of Excel. We all salute you. Thanks always for your input.

Jim

 

[T. Valko]

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 one of these...

If you know for certain that you will never insert new columns in front of the referenced range then:

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

If you might insert new columns in front of the referenced range then:

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

 

[jim may]

Ditto to you too BIFF !!! Thanks for your input. Excellant