Sumif Circular reference

Options
cmyers
cmyers
edited 12/09/19 in Formulas and Functions

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:

Comments

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    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? 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭
    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!