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

#### IainClover

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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Fluff

##### MrExcel MVP, Moderator
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

##### Well-known Member
With Fluff's last suggestion, Array enter the formula.

T202009a.xlsm
ABC
133
2A123
3A123
4B254
5B254
6B254
712
812
912
4bb
Cell Formulas
RangeFormula
A1A1=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&""))
C1C1=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

##### New Member
Thank you that worked perfectly

#### Dave Patton

##### Well-known Member
Thanks for the feedback.

#### nemmi69

##### Active Member
=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

Replies
20
Views
441
Replies
5
Views
166
Replies
0
Views
84
Replies
1
Views
269
Replies
0
Views
193

1,128,014
Messages
5,628,142
Members
416,294
Latest member
McStuffins

### 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.

### Which adblocker are you using?

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

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