#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Unique numbers in a column

Options
edited 12/09/19

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.

• Employee
edited 04/04/17
Options

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!

• ✭✭✭
Options

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!

• ✭✭✭
Options

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.

This discussion has been closed.