Circular Reference and Blocked Error when running SUMIF calculations

Kelsey Ciarrocca

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!

David Joyeuse

Hi @Kelsey Ciarrocca

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!