SUMIFS Exclusion Criteria Causing #CIRCULAR REFERENCE Error

System

This discussion was created from comments split from: SUMIFS excluding criteria.

KPH

Hi @Ron Rizzardi

You can have one formula in a column that references the entire column (it will ignore itself). However, once you add another formula that references the whole column it will be also be referencing that first one (which it can't ignore) and the circular reference error will be triggered. It is not able to evaluate the criteria of the SUMIFS before returning the error.

You could add another column for the totals to keep these separate from the numbers being summed.

See:

https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula?_gl=11xd4jnf_gcl_auNjg2OTY5MjA5LjE3MTgwMzQ0NDk._gaMTI5NjQwMjI1MC4xNzEyNDU2OTI3_ga_ZYH7XNXMZK*MTcyNTY2NzU2Mi4zNTAuMS4xNzI1NjcxMTk5LjUxLjAuMA..

If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula.

Ron Rizzardi

Thanks I will try that. I dabbled in that as a solution and remember still having trouble but thought of another variation I had not tried.

KPH

No problem, good luck!