IF statement with multiple conditions

Karenmst

Hi, I am trying to write a formula that returns a specific status (text) depending on checked boxes in 3 other columns. If the column is checked, the status text should be as follows:

[Invoice sent to Contact] = "2. Invoice Sent"

[Payment Received?] = "3. Payment Received"

[Void Invoice?] = "4. Invoice Voided"

None of the above columns checked = "1. Invoice Request Submitted"

The equation I am trying is as follows:

=IF([Invoice sent to Contact?]@row, IF([Void Invoice?], IF([Payment Received?]@row, "3. Payment Received", "4. Invoice Voided"), "2. Invoice Sent"), "1. Invoice Request Submitted")

This equation works with just 3 statuses (below for reference), but when I add the 4th status the equation breaks down and I can't figure out how to configure it correctly. Any help appreciated!

{ 3 status equation that works: =IF([Invoice sent to Contact?]@row, IF([Payment Received?]@row, "3. Payment Received", "2. Invoice Sent"), "1. Invoice Request Submitted") }

Mark.poole

@Karenmst In that case you just adjust the order that you have the IF statements. The way if statements work is " IF this is true then this, if false then this." Once it finds the first true statement it stops checking for more. This formula should work out for you.

=IF([Void Invoice?]@row = 1, "4. Invoice Voided", IF([Payment Received?]@row = 1 , "3. Payment Received", IF([Invoice sent to Contact?]@row = 1, "2. Invoice Sent", "1. Invoice Request Submitted")))

Mark.poole

@Karenmst

=IF([Invoice sent to Contact?]@row = 1, "2. Invoice Sent", IF([Payment Received?]@row = 1 , "3. Payment Received", IF([Void Invoice?]@row = 1, "4. Invoice Voided", "1. Invoice Request Submitted")))

Karenmst

Thank you Mark - That equation works - but I forgot a rather important detail, which is that two columns may be checked at the same time…. once the invoice is sent, the next step would either be for the Payment to be Received OR the invoice to be Voided…. so if the Payment Received or Invoice Voided check box is also checked, that checkbox would override the 'Invoice Sent' Status. As it works now, this equation returns 'Invoice Sent' for all my entries.

Is there a way to add this additional layer of logic to the equation?

Thanks again for your prompt reply!

Karen

Mark.poole

@Karenmst In that case you just adjust the order that you have the IF statements. The way if statements work is " IF this is true then this, if false then this." Once it finds the first true statement it stops checking for more. This formula should work out for you.

=IF([Void Invoice?]@row = 1, "4. Invoice Voided", IF([Payment Received?]@row = 1 , "3. Payment Received", IF([Invoice sent to Contact?]@row = 1, "2. Invoice Sent", "1. Invoice Request Submitted")))

Karenmst

Amazing. Thank you. This did the trick. Much appreciated.

Mark.poole

Of course. I'm glad I could help.