include INDIRECT function into SUMPRODUCT formula

Hi guys,

I have the following formula:
=SUMPRODUCT((Archived!$O1:$O5135=$A2)*(Archived!$H1:$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(Archived!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!$H1:$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