Need help with IF / CONTAINS / AND

nathan.tia

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:

1. Auto Number called LINE-ID
2. Second helper column called ROW# with column formula =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 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!

Jeff Reisman

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.