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!

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Match or Index
I am trying to pull a phone number and email from our master roster that is on another sheet in Smartsheet. I cannot figure out the formula. This is the formula I have tried. =INDEX([EMPLOYEE NAME]@row , MATCH({IMS Master Roster Range 2}), 0)
Workflow Generate Impact Report PDF in Impact Form Response Sheet has become invalid
Hi All, I keep getting this annoying error when I try to trigger a PDF generation. I initially set up to trigger the PDF generation to kick off once the last field the needs data is "not blank". That is when the error started. I modified the sheet and added a yes/no drop down to trigger the PDF generation when the value in…
INDEX COLLECT MIN
Desired result is to pull the Initiative Name where the Days to Due Date is the least (so, due the soonest); then to make an index for the Top 10 Initiatives Due Soonest Currently using this formula: =INDEX(COLLECT({Project Master - Initiative}, {Project Master - Days to Due}, MIN({Project Master - Days to Due})), 1) This…