Count Unique Values

Zac

I have a master sheet that is essentially a list of all the schedules we currently have dealing with projects. Each project has its own project number. All i am trying to do is get a number of the projects we have scheduled to compare to the actual number of projects we have in our queue.

 

Now for the tricky part. Some projects are very large so the schedule was broken up into multiple sheets which equals multiple line items on my master sheet. Is there some type of COUNT formula that will only count the unique values in my project number field?

 

Ex: Lets say i have 10 projects in my queue and i'd like to know how many of those projects have schedules. The list currently shows 8 schedules, however, that doesn't mean 8/10 projects have schedules. One project could have been large and had two schedules so I need a formula that will count my rows but know to only count a given project number once and not multiple times...

 

Any help or ideas would be great! Thanks.

Jamison

Will (can) the like numbers be grouped? If grouped, you could run another column and and put this in there: 

 

=IF(project_number1 = project_number2, 0, 1)

 

This compares row 2 value to row 1 value and if they match, it returns 0, if they are different it returns 1. Drag that formula down and it will automatically change to reflect the next set of rows.

 

Then all you need to do is sum that column and you'll have you number of unique projects. 

 

 

Greg Gates

Jamison's answer works if your projects with the same name are guaranteed to be right next to each other. If that's the case, definitely go with that solution since it's a little simpler than what I'm about to propose. You'll still need to add an extra column for this to work though.

 

If the projects with the same name aren't guaranteed to be right next to each other, we have to be a little creative with our ranges. This formula will check to see if a value appears anywhere in a given range of cells, and it will only count it if it appears exactly once (assuming the names are in a column called "Project Name"):

 

=IF(COUNTIF([Project Name]$1:[Project Name]1, [Project Name]1) <> 1, 0, 1)

 

This means that the first time you come across the project name, the formula will evaluate to a 1. If you see that project name again farther down in your column, it will evaluate to a 0. The last step is the same as Jamison's - just sum the results of this column and you'll get your number of unique projects!

Zac

Thanks guys! I'll definitely try this out. I figured I would have to create another column for this so I had already done that but I just couldn't get the formula figured out. The ones you guys posted make sense though.

 

Thanks again!

Wanda T

I was trying to use this formula (in place of #CountUnique and wasn't getting the desired result.

 

Primary Column = list of company names (sometimes duplicates), I was trying to count the number of Companies in the list (ignoring duplicates).

 

Formula:  =IF(COUNTIF([Primary Column]:[Primary Column], [Primary Column]) <> 1, 0, 1)

Outcome:  Unparseable

 

Tried Formula:  =IF(COUNTIF([Primary Column]:[Primary Column], [Primary Column]:[Primary Column]) <> 1, 0, 1)

Outcome:  0

 

Tried Formula:  =IF(COUNTIF([Primary Column]$1:[Primary Column]1, [Primary Column]1) <> 1, 0, 1)

Outcome: 1

 

My list of companies will start on Row 9 and will be added to over time (is not a defined range)

 

My formula is in Row 2 of another column (First Name).

 

Help is appreciated.  

Thank you

 

Greg Gates

The '$' in a formula prevents the number from automatically changing when you copy the formula down to new rows. If you don't want to actually start checking until row #9, I would put the following in your "First Name" column (starting at row 9):

 

=IF(COUNTIF([Company Name]$9:[Company Name]9, [Company Name]9) <> 1, 0, 1)

 

This formula procedurally expands as you copy it down into new rows. In row 10, it will just check rows 9 and 10. In row 20, it will check rows 9 - 20. Basically it just tells you the first row a name appears in by putting a '1' in this cell. If you want to see how many unique companies you have, you would then get the sum of the values in this column.

 

If that still doesn't seem to work for you, could you post a screenshot of your scenario?

Wanda T

Thank you Greg!   

 

The missing element was "create a new column, input the formula and copy it down for each row".  Perhaps I'm a little thick  I was looking for an 'excel type 'CountUnique' function for a column of data ... and because I was fixated on this I didn't pick up on the need to create a new column (and subsequently hide it).

 

I am not sure if the formula above required your sheet to be 'sorted', I had already done this and have not tested it otherwise (for the next person who uses this - I think Greg's earlier posts says they don't have to be sorted/next to each other.).

 

Ideal or Ask of Smartsheet  =CountUnique(Children())

 

However my problem is solved with the workaround of adding a new column

 

Thanks again - great community support!

Wanda

Oleksandr Koval

Dear Greg, and what about if we need to count unique values in just one part of column? Defined range let's say row 20 to 40? Many thanks in advance!

 

JanAllen

I freaking love you guys...just saved me!

 

J

 

Sail Sabnis

Hey guys, can you tell me why the formula returns 1 even for an empty field? This doesn't help as I use the counter field for reporting on all fields with 1s and empty fields returning 1 gives false data in the report. Any help, please?

 

Sail Sabnis

Guys, any help around this one please?

J. Craig Williams

Yes.

1. Your formula is only looking at one cell, not a range. Back to that in a minute.

That said, I'm not quite sure what your intent is, but it looks like you MIGHT be looking for duplicates.

So here's some hints

A blank is not equal to 1.

If you are looking for 1's then change to this:

=IF(COUNTIF($A$201:$A$201,$A201)=1,0,1)

But, that doesn't really make sense from a 'what are you trying to do?' standpoint.

So ... back to (1)

Let's suppose you are looking through the first 199 rows of column [A] and if you find more than one "1", you want to know. How many you don't care, you just want to check if there is more than one.

=IF(COUNTIF($A$1:$A$199,$A201)>1,1,0)

{I prefer this version, but will keep the row 201 for illustrative purposes

=IF(COUNTIF($A$1:$A$199,$A@row)>1,1,0)

}

Let us know if that helps.

Craig

Sail Sabnis

Hi Craig, 

Thank you for the help. 

Apologies for not clearly mentioning the intent. 

Scenario:

This is a sheet that will be converted into a template and used by many users. 

Users will copy past a list of project names (fetched from a different source) beginning at A201. The list can grow as big as it gets. It will have repeat project names as well. I want to capture all the unique names in this list and then show them in a report. 

Since array formulas doesn't work in Smartsheet, I thought of creating a new column (say Unique projects counter) and return 1 for all the unique values in A. I will then use this condition (unique project counter = 1) in a report to return all the unique values in A column. 

2 problems : 

A) This sheet, eventually being a template, will have an empty A 201 cell before the user copy pastes the list. In that case the report will have an empty A201 field reported as the formula returns 1 for an empty cell. 

My workaround this : 

In report have the following condition:

What :

A is NOT blank 

Unique Projects counter =1 

Ask: This works but I was wondering if I can modify the formula itself. 

The users copy paste / add to the list frequently. For all the new cells added in A column (using copy paste), the corresponding cells in the "Unique Project Counter" column must have a auto-formula automatically set. But, this sometimes work and sometime doesn't. I know, this sounds crazy but the auto-formula does not really work everytime for copy pasted data (since it does not involve hitting the "Enter/return" key, I guess?) 

Ask: Is there something I am doing wrong or this is an issue with the auto-formulas? 

Thank you! 

 

Best Regards, 

Sail Sabnis

 

J. Craig Williams

At this point, I need to step back and ask:

1. why are the users copying and pasting project names into the new sheet?

2. why aren't they unique?

3. what is done with the report, that is what action is taken with the report results?

4. what is located in the the first 200 rows?

My initial impression is that you are trying to solve the symptom, but not the problem. 

In relation to auto-fill, yes, there are issues where it sometimes does not work. I have not seen anything in the Release Notes that this has been addressed.

Craig

Art Schneiderheinze

Any idea on how to use this formula across parent and children rows that checks for unique values among children? 

The same value might appear across parents/children, which is fine, but how can I modify this formula so it finds unique values among children?

Anupriya

You may use COUNT(DISTINCT(Range)) to count the number of unique entries.