Sumproduct changing range

[Hapitaron10]

I am using this function
=SUMPRODUCT(($D$3:$D$1845="NZD")*($E$3:$E$1845))

However the range change everyday how can I adjate the furmula to change acorrdnig to the range change?

thanks

[VBA Noob]

Hi,

Either extend your range down to say 10000 or use a named range to make it dynamic as per the attached link

http://www.contextures.com/xlPivot01.html#Dynamic

VBA Noob

[Carim]

Hi,

You can define your ranges with a "dynamic range" i.e a range which adjusts itself automatically ...

Insert Name Define
give your range a name
in the formula area type

Please Login or Register  to view this content.

HTH
Carim

[daddylonglegs]

I am using this function
=SUMPRODUCT(($D$3:$D$1845="NZD")*($E$3:$E$1845))

With one criteria, SUMIF is better than SUMPRODUCT, i.e.

=SUMIF($D$3:$D$1845,"NZD",$E$3:$E$1845)

to make this variable, assuming you don't have numbers in E1 and E2 and you do have numbers all the way from E3 to your end row, whatever that is

=SUMIF($D$3:INDEX($D:$D,COUNT(E:E)+ROW(D$3)-1),"NZD",$E$3:INDEX($E:$E,COUNT(E:E)+ROW(D$3)-1))