Counting the number of unique different values in a set

[uOttawaGeek]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi everyone, I need to count the number of different values in rows of data.

For example, in 100 rows of data about Gender, there are only two values, F and M, so I would like the function to return 2, even if I select 100 rows all with data.

Another example:
Given the following list:

apple
orange
berry
berry
apple
banana
apple

The function would return 4 since there are four different values, apple, orange, berry banana

Does this function exist in Excel?

Thanks, this will be very helpful to a research project

 

[williamm]

Hi Ottowa,

I'm not aware of a function to do this, but I've gotten the same result this way:

-> Copy data into empty sheet, so original doesn't get damaged,

-> Sort the data column, it doesn't matter how,

-> In the adjacent column put an IF test in each cell, that returns "1" if the cell on the left and the one above are different, "0" otherwise,

-> Fill down so all the data items in the data column are being compared to their neighbors above,

-> Sum up the number of "1" values in the second column and add one for good measure.

I tried this just now, and cell B2 in my sheet contained this formula:

=IF(A1A2,1,0)

Don't forget to sort the data, and to account for spaces, case sensitivity, etc. Good idea to try this technique out first on dummy data where you know the answer ahead of time.

 

[Bob Greenblatt]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel
Hi everyone, I need to count the number of different values in rows of data.

For example, in 100 rows of data about Gender, there are only two values, F
and M, so I would like the function to return 2, even if I select 100 rows all
with data.

Another example:
Given the following list:

apple
orange
berry
berry
apple
banana
apple

The function would return 4 since there are four different values, apple,
orange, berry banana

Does this function exist in Excel?

Thanks, this will be very helpful to a research project

Another way is to do an advanced filter and specify unique only. Then count
the result.