Count total number of different ID values in a Column

IainClover

New Member
Joined
Dec 1, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Dec 1, 2020
Messages
7
Office Version
  1. 2013
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,633
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

nemmi69

Active Member
Joined
Mar 15, 2012
Messages
482
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
=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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

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
Top