Unique numbers in a column

I have a sheet that we add rows to everyday. One peice of data that is added with every new row is a case number (unique identifier to show us who we are working on). How can I get a total of unique case numbers? I simply want a count of all case numbers no duplicates. So if case 12345 is on my sheet 5 times I only wanted it counted once.

Find more posts tagged with

Formulas

Reports

Conditional Formatting

Account and User Management

Comments

Adam Overton

Create a second column, and put a formula in it that counts it if it hasn't appeared before:

So the first item in the second column is 1, because we know it hasn't appeared before.

The second row has the formulate shown:

=IF(COUNTIF([Primary Column]$1:[Primary Column]1, [Primary Column]2) > 0, 0, 1)

If you look at the range around Primary Column, the $1 before the column means always start at row 1, but the regular 1 after means use relative position. So it always means from the first row to the item previous.

The IF says if there are any counted, put in a zero (because we don't want to count again. Otherwise, put in a 1 because this is the first one.

Sum this column to get the number of unique items. Hide it if you don't want to look at it again!

ODT

Hi,

Indeed very useful example. Can I ask a related question? How do I count for unique entries within specified range or time period? Using the example above, say the first 5 rows are day 1 and the next 5 rows are day 1, and i wanted to count the unique values per day?

Thank you!

Karina.Perez

How can I use this formula and incorporate a date range. Such as a cell with a start date (=Today(-30)) and a cell with an end date(=Today)?

To count the instances as individuals but only those that fall within the last 30 days not taking into account the older entries.