Approval Workflow and SUMIF Formula

Options

Hi,

I have a form individuals submit when they request tickets for an event. Our sheet manager can either approve or decline the requests. If approved, I have a formula set up that removes the amount of requested tickets from the total. The request entry is copied to a separate sheet that has a calendar of each event along with the amount of tickets available.

I'm running into an issue when requests are accepted and then later declined. The declined event is copied into my calendar sheet, but I can't figure out how to update my formula to remove the requested tickets. The formula I currently have is: =24 - SUMIF([Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Number of Tickets Needed]:[Number of Tickets Needed]). Below is a screenshot of the copied entries.

The last two entries show approved and then declined, however, my ticket formula only subtracted the 5 tickets from the 24 total, but when the declined row is copied, it doesn't update to reflect that the five tickets are now available again. How can I add a criteria to my ticket formula to re-add tickets to the total when a request is later declined? Or is there some other solution? Thank you!


Best Answer

  • Katy H
    Katy H ✭✭✭✭✭✭
    Answer ✓
    Options

    Definitely!

    =24-SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Approved") - SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Declined")

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

Answers

  • Katy H
    Katy H ✭✭✭✭✭✭
    Options

    Yes! You would convert this to a SUMIFS and only account for requests that have been approved.

    Hoping this works!

    =24-SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Approved")

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

  • danielleelalouf
    Options

    Thanks, Katy! Is there a way to add on to the formula to have it also then subtract the amount of tickets for when a separate line comes in showing declined?


    =24-SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Approved" - [Approval Status]:[Approval Status], "Declined").


    This is definitely not a correct formula but this is the last piece I'm trying to figure out how to squeeze in there.

  • Katy H
    Katy H ✭✭✭✭✭✭
    Answer ✓
    Options

    Definitely!

    =24-SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Approved") - SUMIFS([Number of Tickets Needed]:[Number of Tickets Needed], [Date for Suite Use]:[Date for Suite Use], DATE(2022, 9, 24), [Approval Status]:[Approval Status], "Declined")

    Katy Hall

    Head of Product Management

    ILLA Canna

    LinkedIn

  • danielleelalouf
    Options

    Yes! This solved all my problems. Thank you so much. That was so helpful!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!