#BLOCKED

DocMertens

Has anyone had an issue where a cell with SUMIF changes to #BLOCKED when a cell in the range changes? I have had this periodically happen. I will cut the SUMIF formula and paste it right back into the same cell, which looks to fix the issue until another value changes. Also, it looks like closing and reopening the sheet will fix the issue at least it has for now. I did confirm there are only numeric values in the range.

Genevieve P.

Sometimes if there's a train of formulas, the final one takes a little longer to calculate and can show an error until it catches up. For example if the column it's referencing has a formula (like the one you posted), but then that one is also referencing a column with a formula (ex. the Organization column or the Coal column has a formula), and then one of those is also referencing a formula, and so on.

I'm glad to hear it's resolved now! If it happens again, please let us know.

Genevieve P.

Hi @DocMertens

Does the column that the SUMIF is referencing contain a formula as well? This error can be caused by a domino effect: the referenced range has an error in one cell so then the summary formula has an error as it looks through the entire column.

If you do have a formula in the referenced column, can you paste it here? We could add an IFERROR statement around it so it would return a blank cell if there's an error, which would resolve the issue with your SUMIF.

Cheers,

Genevieve

DocMertens

Thanks, Genevieve. There is an formula in the reference range, which is another SUMIF.. The formula is SUMIF($Organization71:$Organization74, CONTAINS("Cost", @cell), [Coal]71:[Coal]74). But that formula is not showing an error and displaying the correct calculation.

What has me puzzled is this: I get the BLOCKED error in a cell with the SUMIF formula. I cut the formula from the cell to clear the cell. I then paste the exact formula back into the same cell. It calculates just fine. I could repeat this every time until I closed the SmartSheet and reopened it. Then it stopped and I cannot repeat it now.

I was not going to post this to the Community since it seemed to resolve itself, but thought maybe others have had it happen and know the cause.

Thanks, again.

Genevieve P.

Sometimes if there's a train of formulas, the final one takes a little longer to calculate and can show an error until it catches up. For example if the column it's referencing has a formula (like the one you posted), but then that one is also referencing a column with a formula (ex. the Organization column or the Coal column has a formula), and then one of those is also referencing a formula, and so on.

I'm glad to hear it's resolved now! If it happens again, please let us know.

Davicd.C

I had the exact same problem and it was driving me nuts. I closed and reopened the sheet and now it's working fine. Thanks for taking the time to post your issue and the resolution.