Help using autofilter & Sumproduct

  • Thread starter Thread starter Scott
  • Start date Start date
S

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?
 
Advanced Filter to filter the unique records, then use Auto Filter to filter
your criteria, then use Subtotal function to count.
 
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.
 
Worked a treat, thank you

T. Valko said:
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.
Click to expand...
 
Back
Top