Error when using multiple SUMIFS in one column

User251

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 Full-Time (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.

Jeff Reisman

@User251

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")

Jeff Reisman

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.

Jeff Reisman

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 Ctrl-C/Ctrl-V 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.

Jeff Reisman

@User251

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")

User251

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.

Jeff Reisman

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.

Jeff Reisman

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 Ctrl-C/Ctrl-V 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.

User251

Thank you so much @Jeff Reisman for the suggestions. I truly appreciate your help! Thank you!