Using COUNTIF function for the flexible range on cells

0
votes

Good morning,

I would like to use the COUNTIF function for the flexible cell range, based on the number of the row.

If my record OR533 is based on the row 4 as per the formula:

 =MATCH(A2,Tracker!A3:A4000)

then the value on my cell W7 is 4.

Now I would like to incorporate this cell value into the COUNTIF function.

My counting function looks like this:

 =COUNTIF(D3:D9,"Julian")

but in this event it counts from the row 1, which is D3. I want now this function working from the row, where my record is based, which is 4 (D7 cell).

I tried to write:

 =COUNTIF(D3:(D9-4),"Julian")
 =COUNTIF(D3:(D&W7), "Julian")

None of them were working.

I found some solution in VBA

Worksheet Function Countifs for resizable range

but I am wondering is it possible to reach without VBA this time? I would like to have the COUNTIF function working only to the specified row (marked in red on the image) instead of the last row.

enter image description here

1
excelexcel-formula
You can do that through the volatile Indirect function, but better would be semi-volatile Index usage. - JvdV
how can I do this? - MKR
I'm not behind a pc, but the syntax would be something like =COUNTIF(INDEX(D:D,MATCH(....)):D9,.... - JvdV
I would say there is no other function that would allow this besides INDIRECT, but then again, I'm not a 100% sure @SJR - JvdV
@SJR OFFSET, INDEX, XLOOKUP (which is basically INDEX MATCH as a single, official function), INDIRECT - they all return References instead of Values (i.e. ISREF(OFFSET(..)) would return TRUE). CHOOSE can too, depending on what arguments you pass - Chronocidal

1 Answers

2
votes

So to follow-up on my comment. A semi-volatile way would be to use INDEX:

=COUNTIF(INDEX(D:D,MATCH(A2,Tracker!A3:A4000)+3):D9,"Julian")

Beware that your use of MATCH currently might yield unwanted results. For an exact match include the last parameter > MATCH(A2,Tracker!A3:A4000,0). Obviously you could also refer to W7 if you so desire.

And if you want some background on volatile, semi-volatile and non-volatile you could have a read here.