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.
Comments
-
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!
-
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!
-
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.
