Need help with IF / CONTAINS / AND


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
    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.


    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!