Sum Formula containing keywords?

lcain

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.

ker9

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)

ker9

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)