Approval Workflow and SUMIF Formula

danielleelalouf
danielleelalouf ✭
inFormulas and Functions

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!

Snip for Smartsheet Question.PNG


Tags:
· Share on Facebook Share on Twitter

Best Answer

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

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

    · Share on Facebook Share on Twitter

Answers

  • Katy H
    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")

    · Share on Facebook Share on Twitter
  • danielleelalouf
    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.

    · Share on Facebook Share on Twitter
  • Katy H
    Katy H ✭✭✭✭✭
    Answer ✓

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

    · Share on Facebook Share on Twitter
  • danielleelalouf
    danielleelalouf ✭

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

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
@Christopher Flemings<\/a> <\/p>

=IF([CONDITIONAL LICENCE]@row, DATE(if(MONTH([Determination Date]@row)=12,YEAR([Determination Date]@row)+1,YEAR([Determination Date]@row)), MONTH([Determination Date]@row) + 1, DAY([Determination Date]@row)), \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-05 19:15:44","updateUser":{"userID":126337,"name":"Christopher Flemings","url":"https:\/\/community.smartsheet.com\/profile\/Christopher%20Flemings","photoUrl":"https:\/\/lh3.googleusercontent.com\/-6uhStWCmgRc\/AAAAAAAAAAI\/AAAAAAAAAAA\/ACHi3rcm-Jq0CJqPMi7PAdar4X8wZHjMlw\/mo\/photo.jpg","dateLastActive":"2022-12-05T19:43:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":98391,"type":"question","name":"Help w\/multiple AND NOT ISBLANK date field formula","excerpt":"Hi team - I have 6 date column fields that are automatically formatted (record a date) when a Status field changes. Let's just call those columns: Date1 Date2 Date3 Date4 Date5 Date6 I created a check box \"helper column\" that I'd like to create a formula that looks across those fields to determine if ALL of them have a…","categoryID":322,"dateInserted":"2022-12-04T15:40:46+00:00","dateUpdated":null,"dateLastComment":"2022-12-05T14:52:40+00:00","insertUserID":118864,"insertUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-12-05T14:51:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":118864,"lastUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-12-05T14:51:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":30,"score":null,"hot":3340422806,"url":"https:\/\/community.smartsheet.com\/discussion\/98391\/help-w-multiple-and-not-isblank-date-field-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98391\/help-w-multiple-and-not-isblank-date-field-formula","format":"Rich","lastPost":{"discussionID":98391,"commentID":353142,"name":"Re: Help w\/multiple AND NOT ISBLANK date field formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/353142#Comment_353142","dateInserted":"2022-12-05T14:52:40+00:00","insertUserID":118864,"insertUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-12-05T14:51:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-05T14:51:25+00:00","dateAnswered":"2022-12-04T15:45:20+00:00","acceptedAnswers":[{"commentID":353088,"body":"

Hey @jmo<\/a> <\/p>

Are your date fields contiguous (all side by side?). I'm assuming they are. If not, each date field will have to be added to the COUNTIFS individually<\/p>

=IF(COUNTIFS([Date1]@row:[Date6]@row, ISDATE(@cell))=6, 1)<\/p>

Will this work for you?<\/p>

Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-05 14:51:25","updateUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-12-05T14:51:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":98384,"type":"question","name":"using OR and multiple expressions","excerpt":"I created this statement using OR and CONTAINS to select a value between 2 expressions - Stage 1 or Stage 2: =IF(OR(CONTAINS(\"Stage 1\", Function@row), CONTAINS(\"Stage 2\", Function@row)), IF([Example Result]@row >= -30, 1)) This works fine for recognizing and providing results for Stage 1 & 2 logical expressions. I want to…","categoryID":322,"dateInserted":"2022-12-02T21:55:26+00:00","dateUpdated":null,"dateLastComment":"2022-12-05T19:26:17+00:00","insertUserID":137060,"insertUser":{"userID":137060,"name":"Rick Girard","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Rick%20Girard","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HMfRH3j3jTw!bqDQWyzsXGY!XokT-B0EOdM","dateLastActive":"2022-12-05T19:24:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":137060,"lastUser":{"userID":137060,"name":"Rick Girard","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Rick%20Girard","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HMfRH3j3jTw!bqDQWyzsXGY!XokT-B0EOdM","dateLastActive":"2022-12-05T19:24:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":19,"score":null,"hot":3340287703,"url":"https:\/\/community.smartsheet.com\/discussion\/98384\/using-or-and-multiple-expressions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98384\/using-or-and-multiple-expressions","format":"Rich","lastPost":{"discussionID":98384,"commentID":353203,"name":"Re: using OR and multiple expressions","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/353203#Comment_353203","dateInserted":"2022-12-05T19:26:17+00:00","insertUserID":137060,"insertUser":{"userID":137060,"name":"Rick Girard","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Rick%20Girard","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HMfRH3j3jTw!bqDQWyzsXGY!XokT-B0EOdM","dateLastActive":"2022-12-05T19:24:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-05T19:26:31+00:00","dateAnswered":"2022-12-03T01:15:13+00:00","acceptedAnswers":[{"commentID":353070,"body":"

Hey @Rick Girard<\/a> <\/p>

It looks like a parenthesis is missing to close off the OR statement.<\/p>

=IF(OR(CONTAINS(\"Stage 1\", Function@row), CONTAINS(\"Stage 2\", Function@row), CONTAINS(\"Stage 3\", Function@row))<\/strong>, IF([Example Result]@row >= -30, 1))<\/p>

Will this work for you<\/p>

Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-05 19:26:31","updateUser":{"userID":137060,"name":"Rick Girard","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Rick%20Girard","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HMfRH3j3jTw!bqDQWyzsXGY!XokT-B0EOdM","dateLastActive":"2022-12-05T19:24:55+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">