Sum Formula containing keywords?

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.



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)

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)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!