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 ???

 

[nemmi69]

Try

=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A100,A2:A100,0),ROW(A2:A100)-ROW(A2)+1)>0))

 

[IainClover]

Try

=SUMPRODUCT(--(FREQUENCY(MATCH(A2:A100,A2:A100,0),ROW(A2:A100)-ROW(A2)+1)>0))

Thanks

I copied the formula into the cell but got the dread "#N/A"

Iain

 

[Fluff]

Do you have any blank cells in A2:A100?

 

[IainClover]

Yes

 

[Fluff]

In that case try

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

 

[IainClover]

In that case try

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

Thank you for your help, now unfortunately I get "#Value!"

Sorry to be a pain, but I couldn't find anything else in the threads that worked, hence my question

 

[Fluff]

Do you have any #VALUE! errors in A2:A100?

 

[IainClover]

Do you have any #VALUE! errors in A2:A100?

There are no #Value! errors in the column, there are cells that have an ID entry, and cells that are blank, awaiting data entry.

 

[Fluff]

Do you have any cells with more than 255 characters?