Count unique occurrences: Pivot and Index/Match – how to make it dynamic?!

[Geoff.]

I am hoping you guys can help. I've been stuck with formulas to count the number of unique occurrences that are way too resource intensive. So far the closest solution I've found is using pivot tables, but I can’t seem to get it completely right.

I am using this formula to pull unique counts from a pivot table:

=COUNTIFS(INDEX($5:$9,MATCH(K20,$A$5:$A$9,0),0),"<>"&"",$4:$4,">="&L20,$4:$4,"<="&M20)

I just can't figure out how to make its references to the pivot table dynamic!?
I don't know why, it looks like it should be simple enough…

Can you please help?

Here is more information about my set up. The sample file is also attached here (Attachment: uniquecount-corrected-3.xlsx)

This count of unique occurrences is a small part of a large macro.

The pivot table is set up this way:
Row labels: names
Column labels: days
Values: count of days

The data that is processed by my macro looks like this:
Column 1: names
Column 2: date range (FROM)
Column 3: date range (TO)
Column 4: unique count formula (as below and same as above)

=COUNTIFS(INDEX($5:$9,MATCH(K20,$A$5:$A$9,0),0),"<>"&"",$4:$4,">="&L20,$4:$4,"<="&M20)

How can I replace these with dynamic named ranges:
'$5:$9'
'$A$5:$A$9'
'$4:$4'

I have tried but I either obtained '#VALUE' or 'REF!' results.

Here is my dynamic range formula for:
'$4:$'4 ->

=pvt!$A$4:INDEX(pvt!$A$4:$NC$4,COUNTA(pvt!$A$4:$NC$4))

'$A$5:$A$9' ->

=pvt!$A$5:INDEX(pvt!$A$5:$A$105,COUNTA(pvt!$A$5:$A$105),1)

'$5:$9' -> ???
I tried this but it seems wrong ->

=pvt!$A$4:INDEX(pvt!$4:$104,COUNTA(pvt!$A:$A), COUNTA(pvt!$4:$4))