Unable to determine why this formula is blocked

Options
Gabrielle A.
Gabrielle A. ✭✭
edited 04/10/23 in Formulas and Functions

I am doing a SUMIFS to add up the committed amount of a certain part of the budget. I am using this equation SUMIFS(Committed:Committed, [Sub-category]:[Sub-category], "IW", Level:Level, "2")


However, none of the criteria I refer to is an equation itself so I am curious why I get a "blocked" error?

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    Do you have an error in one of your referenced columns? Like #CIRCULAR REFFERENCE or similar?

    You typically get the #blocked error b/c it can't evaluate cells in your columns due to errors.

    Otherwise, you can post a screenshot of your sheet here and we can take a look.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Gabrielle A.
    Gabrielle A. ✭✭
    edited 04/10/23
    Options

    Hi Ryan,


    I am not sure where the errors could be though? I have been trying to access where it is but am not able to find it.

    I basically want to set up an equation for those in level 1 with the criteria of that category and that sublevel who have a level =2 to total. Thus if another row is added we do not manually have to change the equation since it only works when the range refers to the specific row.


    Hoping you can help me out!


    Thanks!

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    See that circular reference error in Committed? That's causing your problem. Why does that have a cir ref?

    Also, your original formula says "[Sub-category]" but I see Sublevel, unless you have the [Sub-category] column hidden or off screen.

    Where are you wanting your formula to go? In which column?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Gabrielle A.
    Gabrielle A. ✭✭
    edited 04/10/23
    Options

    Oh okay I see. I since deleted that equation.


    I would like the equation to be in "committed" column in the row titled Investigator workshops. I have since updated it to this:

    =SUMIFS(Committed:Committed, Level:Level, "=2", Cat:Cat, "CT", Sublevel5:Sublevel7, "IW"


    now I get the error message of "Circular reference"

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    Right, you can't reference the same column you're in like that. But if you're looking to bring in the children of that parent row # 4, you can put this in the Committed Column in Row 4:

    =SUMIFS(CHILDREN(Committed@row), CHILDREN(Level@row), @cell = 2, CHILDREN(Cat@row), @cell = "CT", CHILDREN(Sublevel@row), @cell = "IW")

    Is that what you were looking for?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!