Sumif Circular reference

cmyers

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!

 

Mike Wilday

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? 

J. Craig Williams

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