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.

Find more posts tagged with

Accepted answers

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)

All comments

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.