I need a formula that looks at 2 different columns to determine if the project is Closed or Open

GeorgiaRygiel

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?

Ramzi

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

Hollie Green

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

Nick Korna

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! 😊

GeorgiaRygiel

@NickKorna this was perfect!

Thanks so much

Paul Newcome

@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"))