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

Options
✭✭✭✭

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

Hey Paul,

It will be in the same sheet.

• ✭✭✭✭✭✭
Options

Try something like this:

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

• ✭✭✭✭
Options

Hi Paul,

Thank you. It works!

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
edited 05/31/23
Options

hi Paul,

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

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!