Help using autofilter & Sumproduct

[Scott]

Hi

I'm using this formula to add up unique fields (peoples names who appear
many times)

=SUMPRODUCT((L3:L2052<>"")/COUNTIF(L3:L2052,L3:L2052&""))

Is there a way to adapt this so that when I auto filter it will only add up
the visible unique cells?

 

[Teethless mama]

Advanced Filter to filter the unique records, then use Auto Filter to filter
your criteria, then use Subtotal function to count.

 

[T. Valko]

Try this array formula** :

=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(L3,ROW(L3:L2052)-ROW(L3),)),MATCH(L3:L2052,L3:L2052,0)),ROW(L3:L2052)-ROW(L3)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Scott]

Worked a treat, thank you

Try this array formula** :

=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(L3,ROW(L3:L2052)-ROW(L3),)),MATCH(L3:L2052,L3:L2052,0)),ROW(L3:L2052)-ROW(L3)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[T. Valko]

You're welcome. Thanks for the feedback!