Couting occurences of different text

[PG]

Hello Excelers,

I have a column of text similar to the one shown below. I am trying
to find a way to oount the number of different occurences. In this
case, I have AAA, BBB, CCC, DDD and SSS so I am looking for a function
that would return 5. I dont want to know the number of each, just the
number of different values.

Any help would be greatly appreciated.

Paul

AAA
BBB
AAA
BBB
CCC
DDD
AAA
AAA
SSS

 

[Voodoodan]

Hi,

You could try 'Subtotals' within the 'Data' menu.

Hope it helps,
Dan.

 

[David McRitchie]

Hi Paul,
You could use this formula in B1
=IF(COUNTIF(A$1:A1,A1)=1,1,0)
and copy down with the fill-handle . Then in cell B10
=SUM(B$1:OFFSET(B10,-1,0))

 

[PG]

David,

That is a great solution. Took me a second to figure out why it
worked. Very clever. Thanks all for the help.

Paul

Hi Paul,
You could use this formula in B1
=IF(COUNTIF(A$1:A1,A1)=1,1,0)
and copy down with the fill-handle . Then in cell B10
=SUM(B$1:OFFSET(B10,-1,0))
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hello Excelers,

I have a column of text similar to the one shown below. I am trying
to find a way to oount the number of different occurences. In this
case, I have AAA, BBB, CCC, DDD and SSS so I am looking for a function
that would return 5. I dont want to know the number of each, just the
number of different values.

Any help would be greatly appreciated.

Paul

AAA
BBB
AAA
BBB
CCC
DDD
AAA
AAA
SSS