# Row sum

#### buffalo

##### Board Regular
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
hi!
try using indirect.

INDIRECT("A2:A" & d1)

thanks!

buffalo said:
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))

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

Replies
14
Views
375
Replies
16
Views
417
Replies
4
Views
331
Replies
3
Views
2K
Replies
5
Views
399

1,203,402
Messages
6,055,185
Members
444,768
Latest member
EMGVT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back