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

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:

Formula:

Please Login or Register  to view this content.

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)

Formula:

Please Login or Register  to view this content.

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 ->

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

Thanks Ford.

Your 1st suggestion is precisely why I came here for help. I am trying to use dynamic ranges instead of absolute ranges that cover entire rows and columns.

As for your second suggestion, I'm not sure why it came out as a picture. I clearly tried to upload an excel file. I am attaching it again here: uniquecount3.xlsx.

Let me know what you think.

Any thoughts?