Unique items in selection AutoFilter

[Albert 1]

When I make a selection with AutoFilter, I can count the items in column A with (f.i.): =SUBTOTAL(2,A2:A3000) .
Is it possible (preferably with a formula) to count the unique items in this selection?
Thanks in advance.

[Aladin Akyurek]

What are criteria that you use for this specific selection whose uique items you want to count?

[Albert 1]

If possible (but maybe it isn't) I would like to know it for numeric values and alpha-numeric values as well.

[Aladin Akyurek]

On 2002-05-03 12:24, Albert 1 wrote:
If possible (but maybe it isn't) I would like to know it for numeric values and alpha-numeric values as well.

Are those the criteria that you used in AutoFilter?

Anyway, not sure but you probably want [i] a count of values of numeric type (meaning: numbers, dates, and times) and, separately, [ii] a count of alphanumeric values (text values plus anything that is entered in a cell whose underlying format is text):

=SUMPRODUCT((ISNUMBER(A2:A100)+0) for [i]

=SUMPRODUCT((ISTEXT(A2:A100)+0) for [ii]

And,

=SUM(IF(ISNUMBER(A2:A100),1/COUNTIF(A2:A100,A2:A100)))

array-entered, gives you a count of unique values of numeric type;

=SUM(IF(ISTEXT(A2:A100),1/COUNTIF(A2:A100,A2:A100)))

array-entered, gives you a count of unique values of alphanumeric type.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-05-03 12:51 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-03 17:17 ]

[Albert 1]

I'll try to explain better what I mean with an example.
I have 800 records; column A contains numbers, B names, C addresses and D domiciles.
After I have made a selection with AutoFilter (this selection contains 30 records), all cells in column D contains the same domicile, but column A can contain same numbers and column B same names.
I would like to know, after I have made that selection with AutoFilter (preferably with formulas) and separate:
1. How many unique numbers there are in column A in the selection of 30 records, and
2. How many unique names there are in column B, also in the selection of 30 records.
From earlier topics I remember your answer:
{=SUM(IF(LEN(A2:A800),1/COUNTIF(A2:A800;A2:A800)))}
but this formula doesn't work after I have made the selection with AutoFilter (it looks at alle records and not only the selection of 30).


[ This Message was edited by: Albert 1 on 2002-05-03 14:23 ]

[Aladin Akyurek]

On 2002-05-03 14:21, Albert 1 wrote:
I'll try to explain better what I mean with an example.
I have 800 records; column A contains numbers, B names, C addresses and D domiciles.
After I have made a selection with AutoFilter (this selection contains 30 records), all cells in column D contains the same domicile, but column A can contain same numbers and column B same names.
I would like to know, after I have made that selection with AutoFilter (preferably with formulas) and separate:
1. How many unique numbers there are in column A in the selection of 30 records, and
2. How many unique names there are in column B, also in the selection of 30 records.
From earlier topics I remember your answer:
{=SUM(IF(LEN(A2:A800),1/COUNTIF(A2:A800;A2:A800)))}
but this formula doesn't work after I have made the selection with AutoFilter (it looks at alle records and not only the selection of 30).


[ This Message was edited by: Albert 1 on 2002-05-03 14:23 ]

Try:

In G1 enter: the domicile of interest

{=SUM(IF(D2:D800=G1,1/COUNTIF(A2:A800;A2:A800)))}

for the numbers. Follow the same logic for the names. Hope you don't have blanks in the ranges of interest.

Aladin

[Albert 1]

That's it!!
I thought there was no solution with a formula, but you found it.
Thank you very much for your effort, Aladin!