Sum of Children and Cells with Multiple Criterion

Screen Shot 2020-04-13 at 5.10.38 PM.png

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

=SUMIFS(CHILDREN(AMOUNT@row), CHILDREN(STATUS@row), AND(@cell <> "PAID", @cell <> "VOID", @cell <> "OTHER DEPARTMENT"))

Thank you for all of your assistance with this.😅

Find more posts tagged with

Accepted answers

All comments

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?

Lily Alvarez

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)

Screen Shot 2020-04-14 at 9.24.06 AM.png

Screen Shot 2020-04-14 at 9.23.10 AM.png

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?

Lily Alvarez

Hi Paul,

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

Screen Shot 2020-04-15 at 3.42.15 PM.png

Screen Shot 2020-04-15 at 3.41.56 PM.png

Thank you so much for your help with this.

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.