# Count unique values if a criteria is met

Options

Hello,

I tried to look to questions related to this, but honestly I don't understand the logic.

I need to count the unique values of the column "Supplier Name" only if they have an "Owner" assigned.

«1

• Options

Genevive, you saved me! It totally works.

Also, with the detailed explanation, I was able to create another extra formulas that I needed.

Thank you so much!

• Employee
Options

So glad to hear that! Happy to help 😊

October 8 - 10, Seattle, WA | Register now

• edited 12/07/20
Options

Hi,

I tried using the COUNT(DISTINCT(COLLECT function to determine how many unique values based on a Date column. I need to count the unique Batch Numbers that were Received on Date = TODAY.

This is the formula I came up with: =COUNT(DISTINCT(COLLECT({Batch Number}, {Date: Received}, TODAY())))

It returns 1, which I know is incorrect. It seems to work when I add days before or after TODAY. For example, when I count the unique Batch Numbers Received TODAY(-4), it returns the correct number.

Any ideas?

• Employee
Options

Is it possible that there is a timezone difference which could be the cause of this? The TODAY function is set to UTC time.

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭
Options

@Genevieve P. I just want to say you are amazing

what a formula, i just used it for my own purposes and it's working!

genuine question, how can i develop this knowledge on smartsheet to use formulas like this please?

• Employee
Options

Haha, thank you! I'm so glad the formula worked for you as well.

I've learned a lot right here in the Community, there are a lot of solutions built here by formula gurus much wiser than I!

There are a number of other resources as well:

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭
Options

Hi @Genevieve P. wow, thank you for all this information. You are an amazing person!

• Options

Amazing instruction! It saved me!!!

Thank you very much for sharing!!!!!

• ✭✭✭✭
edited 08/23/22
Options

Looked good, but no matter the Criteria, it only returns a "1". Even if my criteria is something that is not part of my range.

• Employee
Options

Hi Todd,

Would you be able to explain further? It would be helpful to see the exact formula you're using, along with the sheet you're referencing (but please block out sensitive data).

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
edited 08/24/22
Options

I'm working on a sheet summary field. I have two columns. [Area] and [City] values for [Area] are region based Northeast, South, West, etc..... it is a Text/Number field. There is a one to many relationship between [Area] and [City] also formatted as Text/Number. I'm trying to count how many unique cities there are in a particular Area. I would put an example of a formula, but in my mind I've attempted every conceivable combination of COUNTIF, DISTINCT, HAS, COLLECT, JOIN, SUMIF, MATCH, etc.... Clearly I have not tried them all, but I do believe I have received the full range of rejection messages that you have provisioned in your system. 🙃

• Employee
Options

Thanks for clarifying - it sounds like the formula above should work for you!

For example, if you're looking within "North", try something like:

=COUNT(DISTINCT(COLLECT(City:City, Area:Area, "North")))

Let me know if this gives you an error or an incorrect result. If you're still getting 1, could you post a screen capture of a sheet with test-data (like mine above) showing your configuration / set-up for these two columns?

Thanks,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭
Options

Thank you, that did work........ I really appreciate the help.

• Employee
Options