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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!