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


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.


  • Adam Overton
    Adam Overton Employee
    edited 04/04/17

    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
    ODT ✭✭✭


    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. 

This discussion has been closed.