Sumif Circular reference

Options
edited 12/09/19

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!

Tags:

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!