List of names, how many different names do i have

[YukonBrad]

Hello Everyone,

Like most things I do in Excel, it seems like it shouldn't be that hard but, well here I am, again


I have a column full of names, all of the names are repeated at some point. I am trying to find a formula that would tell me the number of different names in that given row. So, if i have 500 rows of names this formula would give the result of XX different names in the 500 rows.

I hope this makes sense, I thought it would be a CountIf formula but I couldn't figure it out.

Many thanks for any help.

 

[wigi]

See:

http://www.mrexcel.com/forum/showthread.php?t=70835

 

[hiker95]

YukonBrad,


Something like this?

Excel Workbook
	A	B	C
1	Oranges		3
2	Oranges
3	Grapes
4	Apples
5	Grapes
6
Sheet2

 

[T. Valko]

Hello Everyone,

Like most things I do in Excel, it seems like it shouldn't be that hard but, well here I am, again


I have a column full of names, all of the names are repeated at some point. I am trying to find a formula that would tell me the number of different names in that given row. So, if i have 500 rows of names this formula would give the result of XX different names in the 500 rows.

I hope this makes sense, I thought it would be a CountIf formula but I couldn't figure it out.

Many thanks for any help.

If you "only" have 500 rows of data try this simple formula:

=SUMPRODUCT((A1:A500<>"")/COUNTIF(A1:A500,A1:A500&""))

If you have "a lot" of rows of data try this array formula**:

=SUM(IF(FREQUENCY(MATCH(A1:A500,A1:A500,0),ROW(A1:A500)-ROW(A1)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes no empty cells within the data range.