Error when using multiple SUMIFS in one column
Hi .
I am trying to find the sum of each group of 5 rows using SUMIFS in Column 1 where the employee's name is John Smith (in cell 3 in Employee Name's column) and the status is FullTime (in the Status column).
=SUMIFS([Column1]:[Column 1], Employee Name: Employee Name, Employee Name3, Status:Status, "Full Time")
The first time I used the formula. it worked.
However, when I used the same formula with a different employee's name for the second group of 5 rows, I got the error #CIRCULAR in the first sum and "BLOCKED" in the second one.
Any help bout this error can be fixed is highly appreciated.
Best Answers

Probably should share a screenshot of your data structure, as there may be a cleaner way to do what you want.
However, right off the bat I can tell you that you need square brackets around column names that are more than one word, and no space after the colon in a range:
=SUMIFS([Column1]:[Column 1], [Employee Name]:[Employee Name], [Employee Name]3, Status:Status, "Full Time")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Ah, I see your issue. You have a formula in a column that references itself. That's a no go. So once one cell goes to an error (#Circular reference), the others go to #Blocked because they can't evaluate the circular reference cell.
Since you are using row hierarchies, this is easy to overcome.
Put this formula in the $$ column on the parent row for each employee:
=SUMIFS(CHILDREN(), CHILDREN(Status@row), "Full Time")
English: Sum the child cells of this $$ parent row where child rows have a Status equal to "Full Time"
This will only consider child rows of the parent row for John Smith. When you copy this formula to the Parent row for Person1 Person1, it will only consider child rows of Person1 Person1.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Just for your edification, the first formula I gave would work (with a little tweak,) if it was placed in its own column:
Add a column called Total or something like that. On the rows where you want the total for that Employee (like is in your screenshots, just in the new Total column,) use the following:
=SUMIFS([$$]:[$$], [Employee Name]:[Employee Name], [Employee Name]@row, Status:Status, "Full Time")
The @row lets your formula work on any row without needing a specific row reference. So you can just CtrlC/CtrlV it to whatever row and it will still work, as long as the Employee Name on that row is the one you want to sum for.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers

Probably should share a screenshot of your data structure, as there may be a cleaner way to do what you want.
However, right off the bat I can tell you that you need square brackets around column names that are more than one word, and no space after the colon in a range:
=SUMIFS([Column1]:[Column 1], [Employee Name]:[Employee Name], [Employee Name]3, Status:Status, "Full Time")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you @Jeff Reisman for the feedback. I omitted the brackets earlier when I copied the formula. Here are 2 screenshots of the error. I have a big sheet with long columns. This is why I chose to use SUMIFS.

Ah, I see your issue. You have a formula in a column that references itself. That's a no go. So once one cell goes to an error (#Circular reference), the others go to #Blocked because they can't evaluate the circular reference cell.
Since you are using row hierarchies, this is easy to overcome.
Put this formula in the $$ column on the parent row for each employee:
=SUMIFS(CHILDREN(), CHILDREN(Status@row), "Full Time")
English: Sum the child cells of this $$ parent row where child rows have a Status equal to "Full Time"
This will only consider child rows of the parent row for John Smith. When you copy this formula to the Parent row for Person1 Person1, it will only consider child rows of Person1 Person1.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Just for your edification, the first formula I gave would work (with a little tweak,) if it was placed in its own column:
Add a column called Total or something like that. On the rows where you want the total for that Employee (like is in your screenshots, just in the new Total column,) use the following:
=SUMIFS([$$]:[$$], [Employee Name]:[Employee Name], [Employee Name]@row, Status:Status, "Full Time")
The @row lets your formula work on any row without needing a specific row reference. So you can just CtrlC/CtrlV it to whatever row and it will still work, as long as the Employee Name on that row is the one you want to sum for.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Thank you so much @Jeff Reisman for the suggestions. I truly appreciate your help! Thank you!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 183 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!