Using Sumif within the sum range
I'm trying to sumif my % complete (and eventually average that number) within a summary row in the same column above. I can do this for 1 row, but when I fill the formula down it creates a circular reference and blocks the row above as well.
Is it possible to solve this without using a helper column?
I've also tried using sumifs and adding additional criteria, but I come up with the same result.
In [% Complete]15
=SUMIF([Schedule Allocation]:[Schedule Allocation], [Task Name]@row, [% Complete]:[% Complete])
Comments
-
Hi Jacob,
As you have found, a circular reference means your formula is referencing itself. Based on the set up of your sheet, I'd suggest starting your sum range at the parent row labeled "Work Group Progress". Although it's not ideal, since new rows are not automatically included in the range, it's the only way to store your formula in the % complete column without getting a #CIRCULAR REFERENCE error. You could also consider storing the formula in one of your existing columns.
If needed, more information on formula error messages can be found here: https://help.smartsheet.com/articles/2476176-formula-error-messages#circularreference
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 67 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!