Sum Formula containing keywords?

lcain
lcain ✭✭
inFormulas and Functions

I'm trying to keep a running sum of the amount of liquid we are holding for our business's transfer log and I am not even sure where to begin!

We have to log transfers (IN) and (OUT) and I would like to write a formula to keep up with this if possible. (Adding the amount in rows with IN and subtracting the amount in rows with OUT)

I have a column that contains the amount of liquid per transfer [Quantity (gal)], and a column that contains phrases and keywords to indicate transfer IN or OUT [Reason for Opening Container].

I have attached a screenshot of the columns for reference.


Screenshot.png


· Share on Facebook Share on Twitter

Best Answer

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 09/22/23 Answer ✓

    Hi@lcain

    See if this formula will work for you - in a separate column/cell.

    It should give you the balance remaining of IN - OUT

    =SUMIFS([Quantity (gal)]:[Quantity (gal)], [Reason for Opening Container]:[Reason for Opening Container], CONTAINS("IN", @cell)) - SUMIFS([Quantity (gal)]:[Quantity (gal)], [Reason for Opening Container]:[Reason for Opening Container], CONTAINS("OUT", @cell))

    Hope this helps! (It is not a running total but a grand total)

    · Share on Facebook Share on Twitter

Answers

  • ker9
    ker9 ✭✭✭✭✭✭
    edited 09/22/23 Answer ✓

    Hi@lcain

    See if this formula will work for you - in a separate column/cell.

    It should give you the balance remaining of IN - OUT

    =SUMIFS([Quantity (gal)]:[Quantity (gal)], [Reason for Opening Container]:[Reason for Opening Container], CONTAINS("IN", @cell)) - SUMIFS([Quantity (gal)]:[Quantity (gal)], [Reason for Opening Container]:[Reason for Opening Container], CONTAINS("OUT", @cell))

    Hope this helps! (It is not a running total but a grand total)

    · 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公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Hi @Fialko66<\/a>,<\/p>

Try this:<\/p>

=IF(AND([Valid To Date]@row < TODAY(30), [Valid To Date]@row > TODAY()), \"EXPIRING\", IF([Valid To Date]@row >= TODAY(), \"CURRENT\", \"EXPIRED\"))<\/p>

Sample:<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Hope this helps - if I've misunderstood something or you've problems\/questions then just post! 🙂<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":110844,"type":"question","name":"Vendor management and ID #","excerpt":"I would like to assign unique vendor IDs in a database according to vendor type (independent column) and sequential numbering. Screenshot of what I am trying to produce below. I have found that the Auto Number column type only accepts a fixed prefix and thus cannot populate different prefixes based on Vendor Type along…","snippet":"I would like to assign unique vendor IDs in a database according to vendor type (independent column) and sequential numbering. Screenshot of what I am trying to produce below. I…","categoryID":322,"dateInserted":"2023-09-27T10:15:28+00:00","dateUpdated":null,"dateLastComment":"2023-09-28T06:37:03+00:00","insertUserID":161575,"insertUser":{"userID":161575,"name":"LGraf","url":"https:\/\/community.smartsheet.com\/profile\/LGraf","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T06:26:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161575,"lastUser":{"userID":161575,"name":"LGraf","url":"https:\/\/community.smartsheet.com\/profile\/LGraf","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T06:26:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":42,"score":null,"hot":3391694551,"url":"https:\/\/community.smartsheet.com\/discussion\/110844\/vendor-management-and-id","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110844\/vendor-management-and-id","format":"Rich","lastPost":{"discussionID":110844,"commentID":397365,"name":"Re: Vendor management and ID #","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397365#Comment_397365","dateInserted":"2023-09-28T06:37:03+00:00","insertUserID":161575,"insertUser":{"userID":161575,"name":"LGraf","url":"https:\/\/community.smartsheet.com\/profile\/LGraf","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-28T06:26:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/2R63S9Z7G5M2\/screenshot-2023-09-27-105524.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-09-27 105524.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-27T12:47:39+00:00","dateAnswered":"2023-09-27T11:37:40+00:00","acceptedAnswers":[{"commentID":397184,"body":"

Hi @LGraf<\/a> <\/p>

Have an auto number column with the number only, then have your Vendor ID # combine it with the Vendor Type:<\/p>

=[Vendor Type]@row +\"-\"+ [Autonumber column]@row <\/p>

Unfortunately I don't think there is a way to have a dynamic prefix in the auto number column itself.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":110839,"type":"question","name":"Countif and Year","excerpt":"=COUNTIF([Date Reported]:[Date Reported], (YEAR(@cell) = YEAR(TODAY()))) Hi, I'm trying to count the items for this year. But every now and then blank rows seem to appear in the sheet and this calc gives an error. Any better ideas?","snippet":"=COUNTIF([Date Reported]:[Date Reported], (YEAR(@cell) = YEAR(TODAY()))) Hi, I'm trying to count the items for this year. But every now and then blank rows seem to appear in the…","categoryID":322,"dateInserted":"2023-09-27T05:44:12+00:00","dateUpdated":null,"dateLastComment":"2023-09-28T08:20:10+00:00","insertUserID":86826,"insertUser":{"userID":86826,"name":"Improve Consulting","url":"https:\/\/community.smartsheet.com\/profile\/Improve%20Consulting","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Bd1ewQBq9MQ!3AFff7A8Bvw!nQq9E9sn7sg","dateLastActive":"2023-09-28T08:29:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":86826,"lastUser":{"userID":86826,"name":"Improve Consulting","url":"https:\/\/community.smartsheet.com\/profile\/Improve%20Consulting","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Bd1ewQBq9MQ!3AFff7A8Bvw!nQq9E9sn7sg","dateLastActive":"2023-09-28T08:29:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3391683862,"url":"https:\/\/community.smartsheet.com\/discussion\/110839\/countif-and-year","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110839\/countif-and-year","format":"Rich","lastPost":{"discussionID":110839,"commentID":397368,"name":"Re: Countif and Year","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/397368#Comment_397368","dateInserted":"2023-09-28T08:20:10+00:00","insertUserID":86826,"insertUser":{"userID":86826,"name":"Improve Consulting","url":"https:\/\/community.smartsheet.com\/profile\/Improve%20Consulting","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Bd1ewQBq9MQ!3AFff7A8Bvw!nQq9E9sn7sg","dateLastActive":"2023-09-28T08:29:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-28T08:29:57+00:00","dateAnswered":"2023-09-28T08:20:10+00:00","acceptedAnswers":[{"commentID":397368,"body":"

Thanks Nick, I'll give them a go.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"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":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • MULTIPLE IF FORMULA AROUND DATES

    Hi: Can anyone help with a Multiple If formula which i cannot get to work please? I have a Valid To Culumn with a date in it and have a Status Column i want toi flag as CURRENT, EXPIRING or EXPIRED depending on the date. I had CURRENT & EXPIRED working but when i try to include the additional IF to show as EXPIRING if the…

  • Vendor management and ID #

    I would like to assign unique vendor IDs in a database according to vendor type (independent column) and sequential numbering. Screenshot of what I am trying to produce below. I have found that the Auto Number column type only accepts a fixed prefix and thus cannot populate different prefixes based on Vendor Type along…

  • Countif and Year

    =COUNTIF([Date Reported]:[Date Reported], (YEAR(@cell) = YEAR(TODAY()))) Hi, I'm trying to count the items for this year. But every now and then blank rows seem to appear in the sheet and this calc gives an error. Any better ideas?