Circular Reference and Blocked Error when running SUMIF calculations
Hello!
I'm trying to put sum statistics by using SUMIF or SUMIFS formulas in the "parent" line of each project I have listed. I want to calculate total car miles and costs of each project.
When I wrote the formula for the first project, it worked. She below:
=SUMIF([Field Site]:[Field Site], "SAN JUAN", Mileage:Mileage) + " Miles YTD"
="$" + SUMIF([Field Site]:[Field Site], "SAN JUAN", Cost:Cost) + " YTD"
When I used this for the other projects, the first line turned to #Circular Reference for both calculations. Second line and every project after that's totals only have ever said #Blocked.
I copied and pasted my first project to get the formatting for the other projects, since they are the same, just [Field Site] and the data numbers change. I've also tried sumifs, with no luck. I've also played around with @row but I don't even know if it can be applied here. Also I did not want to define specific rows, because if a line gets added, it doesn't go into the calculation/ the formula doesn't adjust to add it in.
I would be very grateful if someone could help me identify the root of the issues, and even more so if you could help me with a solution!
Thank you!
Answers
-
The #BLOCKED error happens because it depends on another formula that is returning an error. So, this will get sorted itself if we sort the #CIRCULAR REF error.
#CIRCULAR REFERENCE means the formula cannot proceed because it tries to update on a second cell that is updating on the first cell.
If your formulas are set up in parents row, then I suspect the culprit to be the ranges in both case.
[Mileage]:[Mileage] will refer to the whole column, including the parent row. Which would bring up a #CIRCULAR REF error.
I would suggest to try this instead:
=SUMIFS(CHILDREN(), CHILDREN([Field Site]@row), "SAN JUAN") + "Miles YTD"
="$" + SUMIFS(CHILDREN(), CHILDREN([Field Site]@row), "SAN JUAN") + "YTD"
Hope it helped!
