Approval Workflow and SUMIF Formula

danielleelalouf

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!

Katy H

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 H

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

danielleelalouf

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

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

danielleelalouf

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