Is there a function to count unique items in a list ?

[Sue]

Problem;
I want to count the number of unique records in a column
and output to a cell.

Example;
Column A
1 Apple
2 Orange
3 Orange
4 Apple
5 Pear
6 Pear
7 Orange
8 Apple
9 Pear
10 Pear
11 Apple
12 Orange
Expected output = 3
i.e. Apple + Orange + Pear

Can anyone help?

 

[Andy B]

Sue

Try here:
http://www.cpearson.com/excel/duplicat.htm#CountingUnique

Andy.

 

[Harlan Grove]

I want to count the number of unique records in a column
and output to a cell.

....

http://groups.google.com/[email protected]

 

[Peo Sjoblom]

One way

=SUMPRODUCT((A2:A200<>"")/COUNTIF(A2:A200,A2:A200&""))

 

[Chip Pearson]

Sue,

See http://www.cpearson.com/excel/duplicat.htm#CountingUnique for a variety
of formulas.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

There is an easy way but first you must addin the conditional sum function. Do this by

Step
Go to Tools, then to Add-Ins. Check the Conditional Sum Wizard box and then click ok

Step

Now you can type the following formula in the cell you want your data to appear in

=COUNTIF(A1:A24,"apple")

this will give you the number of times apples appear in the list starting at A1 and ending at A24

You can also go to the function list and look for countif and this will walk you through the process. You will still need to do step 1

 

[Harlan Grove]

There is an easy way but first you must addin the conditional
sum function. Do this by

Step 1
Go to Tools, then to Add-Ins. Check the Conditional Sum Wizard
box and then click ok. ....
You can also go to the function list and look for countif and
this will walk you through the process. You will still need to do step 1.

Not true. COUNTIF and SUMIF are built-in functions. No add-ins needed.
They're available for use whether or not the conditional sum add-in is
loaded.