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?
Answers
-
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.
-
Unfortunately this sheet will populate from an order form so there will be an undetermined number of rows.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!