include INDIRECT function into SUMPRODUCT formula

  • Thread starter Thread starter markx
  • Start date Start date
M

markx

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

referencing a range in Sumproduct function 6
Problem with using INDIRECT with SUMPRODUCT and ROW() 11
Sumproduct & Indirect Functions 9
count if equal to not working? 1
Sumproduct with multiple criteria using indirect formula 4
#REF Error in Named Range with INDIRECT, SUMPRODUCT, and OFFSET 6
sumproduct - validating codes & dates 3
Indirect reference causes statistical function to throw up a #NUM! 10

Back
Top