counting unique values based on a condition with autofiltering

Hi all,

Like all, apologies if this may have been answered already... if so please point me to the right thread...

I have a column that contains unique values and I am able to count the total amount of unique entries that updates when an autofilter is used. The formula I have used is:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(M32,ROW(M32:M6294)-ROW(M32),,1)),
IF(M32:M6294<>"",MATCH("~"&M32:M6294,M32:M6294&"",0))),ROW(M32:M6294)-ROW(M32)+1),1))

My question is, how can I further restrict this to only provide unique values if the condition in say cell I7 appears in column "S"?

I am using excel 2010 if this helps?

Thanks heaps

Sam

Hi @daffodil11 and @Tony Valko

One thing I should have mentioned is that I enter this formula as an array (CNTRL + SHIFT + ENTER).

I have created a "clean" version of the data file and attached it here.

Cell B8 gives me the total number of groups in the range C11:C79. This updates perfectly when you apply filters in header row 10

What I would like to see is a total number groups linked to a specific condition, in this case the ABC segment. So in B3 I would like to see the number of unique groups that are in segment "A".

I hope this helps a little more?

Thanks heaps

Sam

Originally Posted by Tony Valko

Try this...

Array entered** in B3 and copied down:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C$11,ROW(C$11:C$79)-ROW(C$11),0)),IF(C$11:C$79<>"",IF(D$11:D$79=A3,MATCH(C$11:C$79,C$11:C$79,0)))),ROW(C$11:C$79)-ROW(C$11)+1),1))

You can reduce the array formula** in B8 to:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(C11,ROW(C11:C79)-ROW(C11),0)),IF(C11:C79<>"",MATCH(C11:C79,C11:C79,0))),ROW(C11:C79)-ROW(C11)+1),1))

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

Thanks Tony - works a treat!!