Circular Reference and Blocked Error when running SUMIF calculations

edited 09/27/20 in Using Smartsheet
09/27/20 Edited 09/27/20
Answered - Pending Review

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

  • 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!

Sign In or Register to comment.