IF statement with multiple conditions

Options
Karenmst
Karenmst ✭✭
edited 05/29/24 in Formulas and Functions

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

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/29/24 Answer ✓
    Options

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/29/24
    Options

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Karenmst
    Options

    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
    Mark.poole ✭✭✭✭✭✭
    edited 05/29/24 Answer ✓
    Options

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

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Karenmst
    Options

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

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    Of course. I'm glad I could help.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!