address reference in range not working

[vladi305]

I'm using the following formula to keep count until an specifict date, that specific date is located today at P340 cell, this updates and tomorrow or day after tomorrow will be P341 so I can't have this hardcoded


=COUNTIFS($P$2:$P$340,P2&"",$Q$2:$Q$340,Q2&"",$R$2:$R$340,R2&"",$S$2:$S$340,S2&"",$T$2:$T$340,T2&"")

I'm using the following but it's giving me an error within the range:
ADDRESS(MATCH(DATEVALUE("9/2/20"),A:A,0),16)

=COUNTIFS($P$2:ADDRESS(MATCH(DATEVALUE("9/2/20"),A:A,0),16),P2&"",$Q$2:$Q$340,Q2&"",$R$2:$R$340,R2&"",$S$2:$S$340,S2&"",$T$2:$T$340,T2&"")

The error is on the pic attached

 

[Fluff]

How about

P$2:INDEX(P:P,MATCH(DATEVALUE("9/2/20"),A:A,0))

 

[duggie33]

Hi vladi305,

Do the range arguments need to be the same size too? For instance, all other range arguments are from row 2 to row 340, but for column P it is matching a date so it might be P2:P300.

Just a thought,

Doug

 

[vladi305]

How about

P$2:INDEX(P:P,MATCH(DATEVALUE("9/2/20"),A:A,0))

It works fine, thanks again Fluff

=COUNTIFS($P$2:INDEX(P:P,MATCH(DATEVALUE("9/2/20"),A:A,0)),P2&"",$Q$2:INDEX(Q:Q,MATCH(DATEVALUE("9/2/20"),A:A,0)),Q2&"",$R$2:INDEX(R:R,MATCH(DATEVALUE("9/2/20"),A:A,0)),R2&"",$S$2:INDEX(S:S,MATCH(DATEVALUE("9/2/20"),A:A,0)),S2&"",$T$2:INDEX(T:T,MATCH(DATEVALUE("9/2/20"),A:A,0)),T2&"")

 

[vladi305]

Hi vladi305,

Do the range arguments need to be the same size too? For instance, all other range arguments are from row 2 to row 340, but for column P it is matching a date so it might be P2:P300.

Just a thought,

Doug

No, I'm looking for a range from two specific dates to today and then since last year, last 6 months, last 3 months. Thanks anyway for trying

 

[duggie33]

It looks like you adjusted the formula to use the same range size for all of the range arguments.

 

[vladi305]

It looks like you adjusted the formula to use the same range size for all of the range arguments.

The range size grows as more data is added

 

[Fluff]

It works fine, thanks again Fluff

You're welcome & thanks for the feedback.