|
include INDIRECT function into SUMPRODUCT formula
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 |
include INDIRECT function into SUMPRODUCT formula
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 |
| All times are GMT +1. The time now is 11:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com