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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.6K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!