Need a Counting function

Topic

New Reply

I’m looking for something like COUNTA() that will count the number of unique values in a column. It would be OK if the column had to be sorted first, but I’m hoping for something better.

When you turn on AutoFilter in a worksheet and click the arrow on the dropdown box at the top of any column, you see the unique values right there and instantaneously. That leads me to think that the function I want might already exist, or at least be easy to create.

What say the Gurus?

Reply | Quote

Replies

[
What say the Gurus?
[/quote]

Found the below on the MS Web site.

They ar both array formuals.

If your data is in B2 to B10 and contains no blank cells

The formula counts the number of unique text and number values in cells B2:B10 (which must not contain blank cells)

=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))

It is an array formula so enter with CTR +Shift + Enter

OR

Count the number of unique text and number values in cells B2:B10 , but do not count blank cells or text values

=SUM(IF(FREQUENCY(IF(LEN(B2:B10)>0,MATCH(B2:B10,B2:B10,0),””), IF(LEN(B2:B10)>0,MATCH(B2:B10,B2:B10,0),””))>0,1))

It is an array formula so enter with CTR +Shift + Enter

Regards,

Tom Duthie

Reply | Quote

Hi Lou

A simpler function which will count both numbers and text is:

=SUM(1/COUNTIF(A1:A100,A1:A100))

If you confirm it by pressing Ctrl + Shift + Enter

Ensure that the range, A have assumed A1:A100 is the exact range otherwise you will get a #DIV/0! Error

HTH

Reply | Quote

I’m looking for something like COUNTA() that will count the number of unique values in a column. It would be OK if the column had to be sorted first, but I’m hoping for something better.

When you turn on AutoFilter in a worksheet and click the arrow on the dropdown box at the top of any column, you see the unique values right there and instantaneously. That leads me to think that the function I want might already exist, or at least be easy to create.

What say the Gurus?

One other way with non array formula ( just press Enter )

assume your range start from col A row 2

=SUMPRODUCT((A2:A100″”)/COUNTIF(A2:A100,A2:A100&””))

HTH

Reply | Quote

Thanks, guys! I’m very busy at the moment and can’t try your suggestions, but I WILL be back soon.

Reply | Quote