Count total number of different ID values in a Column

[IainClover]

I have searched the forum threads, but can't find an answer to what I assume is a relatively easy question. I have a column of ID values, which are a mix of text and numbers.

I am using Office 2013.

ie Column is A2:A100
ID values will be A123, B254, A123, A287 etc etc ... so an ID might appear more than once

Counting the total number of entries, in the column, is straight forward, but in Cell A101, I want to put a formula that counts the total number of differing or rather different ID values, so that the total only counts each different ID, once.

Does this make sense ???

 

[IainClover]

Do you have any cells with more than 255 characters?

No each ID is a mix of up to 7 characters, with no spaces, commas or anything other than the ID value ie - text and integers

 

[Fluff]

In that case I have no idea why you get that error. The only way I managed was to have either #VALUE! or more than 255 characters in a cell.

 

[Dave Patton]

With Fluff's last suggestion, Array enter the formula.

T202009a.xlsm
	A	B	C
1	3		3
2	A123
3	A123
4	B254
5	B254
6	B254
7	12
8	12
9	12
4bb

Cell Formulas
Range	Formula
A1	A1	=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))
C1	C1	=SUMPRODUCT(--(FREQUENCY(IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1)>0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[IainClover]

Thank you that worked perfectly

 

[Dave Patton]

Thanks for the feedback.

 

[nemmi69]

=SUM(IF(FREQUENCY(IF(A2:A100<>"", MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1),1))

Remember to use ctrl+shift+enter not just enter