View Single Post
  #15   Report Post  
Daniel.M
 
Posts: n/a
Default

=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.