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?

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



"Scott" wrote:

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?

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:L205 2)-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.

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
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?

Worked a treat, thank you

"T. Valko" wrote:

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:L205 2)-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.

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
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?

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
Worked a treat, thank you

"T. Valko" wrote:

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:L205 2)-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.

--
Biff
Microsoft Excel MVP


"Scott" wrote in message
...
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?