Unique Records Count with AutoFilter

[Danni2004]

I’m trying to set up a formula that will give me a count of unique records
and adjusts with autofilter the way that subtotal does.
Here is an example of my data:
A B C
1 Region Weekday PersonID
2 1 TUES 119718
3 3 TUES 76826
4 2 TUES 165236
5 3 WED 89163
6 1 WED 119718
7 3 WED 76826
8 3 THURS 89163
9 1 THURS 119718
10 3 THURS 76826
11 2 THURS 165236

 

[Danni2004]

Also, please note - unable to use Pivot Tables for this worksheet

 

[Bob Phillips]

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C2:C20,ROW(C2:C20)-MIN(ROW(C2:C20)),0,1)),MATCH(C2:C20,C2:C20,0)),IF(SUBTOTAL(3,OFFSET(C2:C20,ROW(C2:C20)-MIN(ROW(C2:C20)),0,1)),MATCH(C2:C20,C2:C20,0)))>0,1,0))

this is an array formula, so ctrl-shift-enter it

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)