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 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.

Tags:

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

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

    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!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    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!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    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.

    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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

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

    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!

  • User251
    User251 ✭✭
    edited 03/24/22

    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
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    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!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    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.

    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!

  • User251
    User251 ✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!