Count total number of different ID values in a Column

IainClover

New Member
Joined
Dec 1, 2020
Messages
11
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 ???
 

Excel Facts

Links? Where??
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
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
Do you have any blank cells in A2:A100?
 

IainClover

New Member
Joined
Dec 1, 2020
Messages
11
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Yes
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 1, 2020
Messages
11
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
Do you have any #VALUE! errors in A2:A100?
 

IainClover

New Member
Joined
Dec 1, 2020
Messages
11
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
Do you have any cells with more than 255 characters?
 

Forum statistics

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

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