SUMIFS Exclusion Criteria Causing #CIRCULAR REFERENCE Error
Answers
-
I have created exclusion criteria for a SUMIFS, but the exclusion criteria does not seem to process except for the row it is on.
On row 37 I want to SUM all the amounts in the rows that do not have a Category of "Total" and do have the Matching Version of "Initial Breakdown 2022" and put the summed value in the Amount column ($572,200). This part works fine:
=SUMIFS(Amount:Amount, [Item Description]:[Item Description], [Item Description]@row, Category:Category, <>"Total", Version:Version, Version@row)
But when I put that same formula in Row 38, I get a #BLOCKED error because row 37 throws a #CIRCULAR REFERENCE error. Why is row 37 even being Summed if I have Criteria that is supposed to exclude processing rows with categories of "Total"?
-
You can have one formula in a column that references the entire column (it will ignore itself). However, once you add another formula that references the whole column it will be also be referencing that first one (which it can't ignore) and the circular reference error will be triggered. It is not able to evaluate the criteria of the SUMIFS before returning the error.
You could add another column for the totals to keep these separate from the numbers being summed.
See:
If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula.
-
Thanks I will try that. I dabbled in that as a solution and remember still having trouble but thought of another variation I had not tried.
-
No problem, good luck!
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!