Hi guys,

I have the following formula:
=SUMPRODUCT((Archived!$O1:$O5135=$A2)*(Archived!$H 1:$H5135=D$1))

However, the range (in this exemple going from row 1 to row 5135) changes
every month, so, if my understanding is correct, I can either:
- change it manually
- use "dynamically named ranges" or
- use "indirect" function that will refer to the =COUNT(Archived!O:O) that I
will put somewhere.

So, my question is: do you know how to include INDIRECT statement into the
above-mentionned SUMPRODUCT function?
Thanks for your help,

Mark

Somthing like:

=SUMPRODUCT((INDIRECT("Archived!$O1:$O"&COUNT(Arch ived!O:O))=$A2)*(INDIRECT("Archived!$H1:$H"&COUNT( Archived!H:H))=D$1))

HTH

markx wrote:
Hi guys,

I have the following formula:
=SUMPRODUCT((Archived!$O1:$O5135=$A2)*(Archived!$H 1:$H5135=D$1))

However, the range (in this exemple going from row 1 to row 5135) changes
every month, so, if my understanding is correct, I can either:
- change it manually
- use "dynamically named ranges" or
- use "indirect" function that will refer to the =COUNT(Archived!O:O) that I
will put somewhere.

So, my question is: do you know how to include INDIRECT statement into the
above-mentionned SUMPRODUCT function?
Thanks for your help,

Mark