Sum of Children and Cells with Multiple Criterion

I am trying to Sum up all of the children cells and one row cells that do not include the following "Status" category.

I thought the following formula (that has been a lifesaver) would add them up since it is the same criteria but I'm not even clear what it's adding up.

The value based on the cells should be = $60,877.98 this is the sum of the parent rows+individual rows with no children


Thank you for all of your assistance with this.😅


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The <> argument is the same as "not equal to", so your current formula is summing all child rows where the status is NOT "PAID", "VOID", or "OTHER DEPARTMENT".

    What row are you wanting to put the formula?

  • Hi Paul,

    You are correct, the issue seems to be when I put it on the master parent row, however could be that its adding the grandchildren and children?

    For now I summed the singles cells using

    =SUM(AMOUNT1475, AMOUNT1474, AMOUNT1446, AMOUNT1445, AMOUNT1444, AMOUNT1443, AMOUNT1430, AMOUNT1412, AMOUNT1341, AMOUNT1125)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Putting it in the row that is highlighted light green will result in it summing all of the rows that contain "Master Mechanical Services Inc" that are indented under it. The reason is that those rows are the Child rows and those rows are blank in the Status column.

    Are you able to provide a screenshot that shows your column names in relation to these rows?

  • Hi Paul,

    Thank you for your feedback. Below are some additional screen shots that I hope will help.

    Thank you so much for your help with this.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to make a copy of the sheet, remove/replace sensitive data as needed, publish as edit by anyone, and then post the published link to the copy here?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!