How to avoid circular reference?

I am trying to sum purchase orders based on where the items are coming from.

=SUMIFS([Bag - 55 Gal Trash]:[Bag - 55 Gal Trash], [Were these provided by the Warehouse or Job?]:[Were these provided by the Warehouse or Job?], "Warehouse")

=SUMIFS([Bag - 55 Gal Trash]:[Bag - 55 Gal Trash], [Were these provided by the Warehouse or Job?]:[Were these provided by the Warehouse or Job?], "Job")

I am trying to put these formulas in the top rows of the Bag column in order to sum all of the order for bags based on where it is being fulfilled. It works if I put the formulas in another column but for the visual aid, I would like it in this location.

Theoretically these formulas don't reference each other because Warehouse and Job will never be in the same cell so there is no overlap of data. Is there another combination of formulas I can use?

Find more posts tagged with

Formulas

Accepted answers

All comments

Hollie Green

If the sheet will have a set number of rows to look at or never go above a certain number of rows you can do the formula as

=SUMIFS([Bag - 55 Gal Trash]3:[Bag - 55 Gal Trash]whatever max row number would be, [Were these provided by the Warehouse or Job?]:[Were these provided by the Warehouse or Job?], "Warehouse")

This should prevent it from looking at the first and second row and prevent the error.

jennika

Unfortunately this sheet will populate from an order form so there will be an undetermined number of rows.

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.