Help using autofilter & Sumproduct
 

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?

Help using autofilter & Sumproduct
 

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?

Help using autofilter & Sumproduct
 

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?

Help using autofilter & Sumproduct
 

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?

Help using autofilter & Sumproduct
 

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?