Sumif function?
I am trying to figure out which equation to use. I would like to Sumif from one sheet (Sheet A) to another sheet (Sheet B) as a column formula.
example story
I have a column for “strain name” and “batch number”. Each batch # will consist of one strain, but there will be multiple buyers per batch. Each batch can have multiple customers buying plants from the batch.
ex.
Batch A has 1000 clones.
- client 1 buys 100 clones
- client 2 buys 500 clones
- client 3 buys 200 clines
- therefore, there are 200 clones left in batch A (this is the number I am looking for)
the source sheet will contain multiple strains with multiple batch numbers. I need to know how many clones are left after each client buys their batch (live plant inventory).
I am looking for a sumif function that will add all of the clone purchases from a given batch number and place it in the cell of another sheet that tracks batches I need one number as there is only one line item in the receiving sheet, which is a “total sold from that batch” column.
the number of clones purchased will be tracked in sheet A and the number of clones remaining will appear in sheet B as a column formula.
hope that makes sense.
Best Answer
-
Try something like this...
=SUMIFS({Sheet A Range to Sum}, {Sheet A Batch Number Column}, [Batch Number Column]@row)
Answers
-
Hi @Sarah_lee123
Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Try something like this...
=SUMIFS({Sheet A Range to Sum}, {Sheet A Batch Number Column}, [Batch Number Column]@row)
-
Thanks so much everyone. Paul Newcome, that was exactly right! Thanks a ton, you're awesome.
-
One more related question. If I want the above equation do be summed only if a checkbox in a different column has been checked. If a checkbox ins not available in the equation, then we could make a drop down column to have a yes/no instead.
=SUMIFS({Sheet A Range to Sum}, {Sheet A Batch Number Column}, AND {Sheet A "Delivered Column" is checked}, [Batch Number Column]@row)
Thanks for your help
-
@Sarah_lee123 You would add it as a new range/criteria set in the same pattern as the batch number.
=SUMIFS({Sheet A Range to Sum}, {Sheet A Batch Number Column}, [Batch Number Column]@row, {Sheet A "Delivered Column"}, 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!