Sumif Circular reference
Hello,
I know this is possible within Microsoft Excel, but do not know if it is possible within smartsheet.
Trying to do a sumif formula across multiple rows and only change "[Assigned To]1" from to "[Assigned To]2", and for row 3 it would be "[Assigned To]3", etc. using this formula
=SUMIF([Assigned To]:[Assigned To], [Assigned To]1, [% Time (Mar)]:[% Time (Mar)])
This worked great for the first row, but then when I copied it to row 2
=SUMIF([Assigned To]:[Assigned To], [Assigned To]2, [% Time (Mar)]:[% Time (Mar)])
I got a "circular reference" error in the row 2 cell, and "Blocked" error in the row 1 cell that worked before I put the new formula in row 2.
Do not have any issues if I use only selected range of cells within the sumif formula such as
=SUMIF([Assigned To]10:[Assigned To]155, [Assigned To]3, [% Time (Mar)]10:[% Time (Mar)]155)
and
=SUMIF([Assigned To]10:[Assigned To]155, [Assigned To]4, [% Time (Mar)]10:[% Time (Mar)]155)
However wanted to sum across entire column instead of limited rows
Thanks!
Comments
-
Seeing an example of the data would be really helpful for providing you support. Can you show us the first couple rows and the error?
-
You can't have a formula that references its own complete column. I assume your formula is in the [% Time (Mar)] column since [Assigned To] should be a Contact List type and formulas are allowed in that column type.
Move the formula to another column and you should be fine.
Also, you can replace the # with @row which is "this row"
=SUMIF([Assigned To]:[Assigned To], [Assigned To]@row, [% Time (Mar)]:[% Time (Mar)])
Lastly, try to get in the habit of using SUMIFS, even for SUM's with only a single criterion.
Craig
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!