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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!