Counting unique items in a range

[Jack Schitt]

Hi all

I found some useful examples in Chip Pearson's site for counting unique
items in a range. These are all based on variations of an array formula of
the form {=sum(expression)}

I tried without success adapting these to the form {=subtotal(9,expression)}
in order to count the unique visible items in an autofiltered range.

Help, please?

 

[Frank Kabel]

Hi
think I 'captured' this formula from Peo Sjoblom: Use the
following array formula (entered with CTRL+SHIFT+ENTER):
=SUM(--IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW
(A2:A100)),,1)),(MMULT(((A2:A100=TRANSPOSE(A2:A100)
*TRANSPOSE(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW
(A2:A100)),,1))))*(ROW(A2:A100)>=TRANSPOSE(ROW
(A2:A100)))),ROW(A2:A100)*0+1)=1)))

 

[Leo Heuser]

Hi Jack

Here's another option:

=SUM((FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A3000)-
ROW(A2),0)),MATCH(A2:A3000,A2:A3000,0)),IF(SUBTOTAL(3,OFFSET(A2,
ROW(A2:A3000)-ROW(A2),0)),MATCH(A2:A3000,A2:A3000,0)))>0)+0)

This is an array formula, and it must be entered with <Shift><Ctrl><Enter>,
also if edited later. If done correctly, Excel will display the formula in
the formula bar enclosed in curly brackets { } Don't enter these brackets
yourself.

The formula can be used with alphanumeric data (text and/or numbers).

 

[Jack Schitt]

Thanks Frank, that worked fine

 

[Jack Schitt]

Thanks Leo, that is great

--
Return email address is not as DEEP as it appears

Hi Jack

Here's another option:

=SUM((FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A3000)-
ROW(A2),0)),MATCH(A2:A3000,A2:A3000,0)),IF(SUBTOTAL(3,OFFSET(A2,
ROW(A2:A3000)-ROW(A2),0)),MATCH(A2:A3000,A2:A3000,0)))>0)+0)

This is an array formula, and it must be entered with

also if edited later. If done correctly, Excel will display the formula in
the formula bar enclosed in curly brackets { } Don't enter these brackets
yourself.

The formula can be used with alphanumeric data (text and/or numbers).

 

[Leo Heuser]

You're welcome, Jack.
Appreciate your feedback

LeoH