Unable to determine why this formula is blocked

Gabrielle A.

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?

Ryan Sides

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.

Gabrielle A.

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

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?

Gabrielle A.

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

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?