=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),
MATCH(A2:A20,A2:A20,0)),ROW(INDIRECT("1:"&ROWS(A2: A20)))-1)0))
The -1 is not required (it puts the 0 count at the resulting array's beginning
instead of at the end). So :
=SUM(--(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),,1)),
MATCH(A2:A20,A2:A20,0)),ROW(INDIRECT("1:"&ROWS(A2: A20))))0))
You can even 'count unique' on more criterias OUTSIDE of the filtered list (if
you see fit), as in:
=SUM(--(FREQUENCY(IF(SUBTOTAL(...)*(cond2)*(cond3),MATCH( ...)),ROW(...))0))
Regards,
Daniel M.
|