# Count total number of different ID values in a Column

#### IainClover

##### New Member
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 ???

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Fluff

##### MrExcel MVP, Moderator
Do you have any blank cells in A2:A100?

Yes

#### Fluff

##### MrExcel MVP, Moderator
In that case try
Excel Formula:
``=SUMPRODUCT(--(FREQUENCY(IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0)),ROW(A2:A100)-ROW(A2)+1)>0))``

#### IainClover

##### New Member

In that case try
Excel Formula:
``=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

##### MrExcel MVP, Moderator
Do you have any #VALUE! errors in A2:A100?

#### IainClover

##### New Member
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

##### MrExcel MVP, Moderator
Do you have any cells with more than 255 characters?

Replies
3
Views
113
Replies
20
Views
1K
Replies
5
Views
314
Replies
3
Views
53
Replies
5
Views
279

1,147,823
Messages
5,743,405
Members
423,792
Latest member
travisds

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back