Need help with IF / CONTAINS / AND
Hello,
Here is my current formula. It accepts, however the first row generates a "#CIRCULAR" error and then the rest of the rows become "#Blocked". Essentially I want the rows to sum some values, and if the row contains "Phase Total," I want it to sum the rows above (single column), to give the totals.
I have two helper columns:
 Auto Number called LINEID
 Second helper column called ROW# with column formula =MATCH([LINEID]@row, [LINEID]:[LINEID], 0)
The formula for the totals column:
=IF(CONTAINS("Analytics", Owner@row), SUM((Count@row * [LOE Used]@row) + (Revisions@row * [RLOE Used]@row)), IF(CONTAINS("All Data Activation", Owner@row), SUM((Count@row * [LOE Used]@row) + (Revisions@row * [RLOE Used]@row)), IF(CONTAINS("Phase Total", [List Item or Activity]@row), SUMIFS(Analytics:Analytics), [ROW#]:[ROW#], <=[ROW#]@row)))
The offending portion of the formula is this line:
IF(CONTAINS("Phase Total", [List Item or Activity]@row), SUMIFS(Analytics:Analytics), [ROW#]:[ROW#], <=[ROW#]@row))
Thank you!
Answers

The #CIRCULAR error is happening because the formula references itself. The circular reference may be direct where the reference is in the formula text itself, or indirect where this formula references a cell which then references back to this cell.
Something in your formula is using a value that is in some way based upon the Totals column where your formula resides.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
Check out the Formula Handbook template!