Row sum

[buffalo]

Hi
I wanna calculate the sum of a column using the sumproduct command

=SUMPRODUCT(((A2:A7)=E2)*((B2:B7)=F2)*(C2:C7))
Calculates the Sum of values in C whenever A has E2 and B has F2

Now, how do I modify it to A2:Ax , etc. where the value of x is stored somewhere in the file, say at D1

Somebody mentioned using "A2:A" & x but I am getting an error if i use it
Maybe some syntactic error. For instance Sum("A2:A" & D1) gives an error.

Thanks

 

[SIXTH SENSE]

hi!
try using indirect.

INDIRECT("A2:A" & d1)

 

[buffalo]

thanks!

 

[Brian from Maui]

Hi
I wanna calculate the sum of a column using the sumproduct command

=SUMPRODUCT(((A2:A7)=E2)*((B2:B7)=F2)*(C2:C7))
Calculates the Sum of values in C whenever A has E2 and B has F2

Now, how do I modify it to A2:Ax , etc. where the value of x is stored somewhere in the file, say at D1

Somebody mentioned using "A2:A" & x but I am getting an error if i use it
Maybe some syntactic error. For instance Sum("A2:A" & D1) gives an error.

Thanks

Because Sumproduct requires all three ranges to be the same size Indirect needs to be applied to all your ranges. Not sure this is the best way to go, but....

=SUMPRODUCT(--(A2:INDIRECT("A"&D1)=E2),--(B2:INDIRECT("B"&D1)=F2),C2:INDIRECT("C"&D1))

 

[Aladin Akyurek]

=SUMPRODUCT(--($A$2:INDEX($A:$A,$D$1)=E2),--($B$2:INDEX($B:$B,$D$1)=F2),$C$2:INDEX($C:$C,$D$1))