Calculate when Status is New and Recipient has received minimum of 1 of each type of Award

Naina Dave

 Hello,

I have a Smartsheet for team recognitions, that has column Recipient (dropdown list with approx. ~100 contact names), Recognition Theme (drop down list with 4 themes) and a column for Status (dropdown with ‘New’ and ‘Processed’). I am trying to build a formula to calculate when Status is New and a Recipient has received minimum of 1 of each type of Recognition Theme. When this criteria is met, the answer to be populated is TRUE. 

Thank you

Naina Dave

Hey Paul,

I am using that field for automated alert notification for someone to take next step actions. I don't necessarily need to change the 1 to TRUE or YES or anything else, but because the alert is going to someone else, I thought it might be less confusing for the person receiving the alert to see words instead of numbers. I have added a new column with auto-calculated field with YES/NO response and basing my alert on that. Thank you very much for your help.

Paul Newcome

Would it be in a separate sheet or in the same sheet?

Naina Dave

Hey Paul,

It will be in the same sheet.

Paul Newcome

Try something like this:

=IF(COUNT(DISTINCT(COLLECT([Recognition Theme]:[Recognition Theme], Status:Status, @cell = "New", Recipient:Recipient, @cell = Recipient@row))) = 4, 1)

Naina Dave

Hi Paul,

Thank you. It works!

However, when I tried to replace the 1 with TRUE but getting error #INVALID COLUMN VALUE.

Paul Newcome

Why are you trying to replace the 1 with TRUE? Are you able to provide a screenshot for reference/context?

Naina Dave

hi Paul,

I have added a workaround column with IF function for YES/NO. Thank you for your help.

Naina Dave

Hey Paul,

I am using that field for automated alert notification for someone to take next step actions. I don't necessarily need to change the 1 to TRUE or YES or anything else, but because the alert is going to someone else, I thought it might be less confusing for the person receiving the alert to see words instead of numbers. I have added a new column with auto-calculated field with YES/NO response and basing my alert on that. Thank you very much for your help.

Paul Newcome

Happy to help. 👍️