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.

Find more posts tagged with

Accepted answers

Paul Newcome

Try something like this...

=SUMIFS({Sheet A Range to Sum}, {Sheet A Batch Number Column}, [Batch Number Column]@row)

All comments

Bassam Khalil

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.

Paul Newcome

Try something like this...

=SUMIFS({Sheet A Range to Sum}, {Sheet A Batch Number Column}, [Batch Number Column]@row)

Sarah_lee123

Thanks so much everyone. Paul Newcome, that was exactly right! Thanks a ton, you're awesome.

Sarah_lee123

Paul Newcome

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

Paul Newcome

@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)

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.