Returning a value of 1 of 3 cells

I have 3 columns. [REQ Amount], [PO Amount], [INV Amount]. I want another column [Amount] to be the most relevant "amount" . At first a REQ is done for say $2. Then a PO is sent for the said REQ but gets messed up and issued for say $3, then my actual cost might be $3. Then the invoice comes in at $2. So when the invoice is paid, I want the $2 to be my most relevant expense amount.

How do I write a statement that says if INV Amount and PO Amount are Null, then REQ Amount, Else if INV Amount is Null then PO Amount, else, INV Amount?

Thoughts?

Best Answer

  • Kayla Crosby
    Kayla Crosby ✭✭✭
    Answer ✓

    Good Afternoon,

    I want through your scenario above and believe an IF statement formula would solve your most relevant "amount" column.

    This is the formula I wrote and the scenarios I tested are blow.

    =IF([INV Amount]@row = [PO Amount]@row, [REQ Amount]@row, [INV Amount]@row)

    Please let me know if this helped!

Answers

  • Kayla Crosby
    Kayla Crosby ✭✭✭
    Answer ✓

    Good Afternoon,

    I want through your scenario above and believe an IF statement formula would solve your most relevant "amount" column.

    This is the formula I wrote and the scenarios I tested are blow.

    =IF([INV Amount]@row = [PO Amount]@row, [REQ Amount]@row, [INV Amount]@row)

    Please let me know if this helped!

  • Hi Kayla, good afternoon to you as well. This kinda works. In the end, the most relevant number is the later one in the process. So I'm encumbering with a REQ $2 from a budget, until a PO is done, then the $2 is superseded by a $3 PO... until an invoice is received then the INV $2 supersedes the PO. Does that make more sense?

  • EUREKA.. i GOT IT! Thanks for the insights!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!