I need a formula that looks at 2 different columns to determine if the project is Closed or Open
Newbie here...
I have 2 columns that are formatted for the green, red, yellow, and gray dot symbols. I am only using the Green, Red, and Gray dots.
The first column is "1st Half or Full Invoice". The second column is "2nd Half Invoice"
Green = Paid; Red = Not Paid; Gray = NA
In both columns, if there are only green or grey dots, then the project is Closed.
But if there is a red dot in either column then the project is Open.
I need a formula that will show that. any ideas?
Answers

Try this  add a new "helper" column with formula =LEFT([1st Half Column]@row,1) + LEFT([2nd Half Column]@row,1)
Make that column a Column Formula. Values in that column would be one of the following "GG", "GR", "RG", or "RR"
Your formula in your "Status" column would be: =IF([Helper Column]@row = "GG", "Closed", "Open")
I hope that helps.
Cheers,
Ramzi
Smartsheet Solutions Architect
www.adapture.com

=IF(COUNTIF([1st Half or Full Invoice]@row, "Red") + COUNTIF([2nd Half]@row, "Red") >= 1, "Open", "Closed")

Hi @GeorgiaRygiel,
This should do what you're after:
=IF(OR(AND([1st Half or Full Invoice]@row = "Green", [2nd Half Invoice]@row = "Green"), AND([1st Half or Full Invoice]@row = "Green", [2nd Half Invoice]@row = "Gray"), AND([1st Half or Full Invoice]@row = "Gray", [2nd Half Invoice]@row = "Gray")), "Closed", "Open")
Sample data:
Hope this helps  if I've got something mixed up or wrong, then just post and we should be able to sort it out! 😊

@NickKorna this was perfect!
Thanks so much

@Nick Korna I think you may have missed an argument for Gray/Green, although I doubt based on context that 1st Half/Full would be "N/A" but a 2nd Half is needed.
@GeorgiaRygiel Here's my simplified take. It basically says...
If both are not blank,
If one is red then it is open, otherwise it is closed.
=IF(AND([1st Half or Full Invoice]@row <> "", [2nd Half]@row <> ""), IF(OR([1st Half or Full Invoice]@row = "Red", [2nd Half]@row = "Red"), "Open", "Closed"))
Help Article Resources
Categories
Check out the Formula Handbook template!