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 LINE-ID
- 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!
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
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!