How to use SUMIF and FIND Function while not including parent cells?

This question has been asked but hasn't been answered how I am trying to use the formula. I am trying to sum a column based on various criteria while not including the parent rows because I don't want the numbers duplicated. This formula is in a cell on a different sheet than where the data originates so I am using references which I tried to show below.

Partners Number of Students Parent Cell (check box)

(reference) {Partners} {No Students} {Parent}

(Parent) Partner 1, Partner 1 10 checked

(Child) Partner 1 5

I want to sum the number of students who worked with Partner 1 but not include the parent cell.

This formula works but includes all rows (parent and child) giving total of 20.

=SUMIF({Partners}, FIND("Partner 1", @cell)>0, {No Students})

I have created a column with a check box to check the parent box (to later exclude via formula) but can't seem to incorporate it correctly in the above formula. Any help would be greatly appreciated as I will have to use this for multiple types of calculations. I want the formula to output 10 (not 20).

Also, I have successfully used this COUNTIFS formula to count how many times a partner name appears in a column excluding the parent cell:

=COUNTIFS({Partners}, FIND("Partner 1", @cell)>0, {Parent}, @cell=0)

This will yield a 2 which is correct.

I just can't seem to make it work for the SUMIF function. Thank You!

Find more posts tagged with

Accepted answers

All comments

Stephanie Lapera

Since my typed graph didn't show up well

David Tutwiler

I think you could nest your {Partners} range inside a CHILDREN() function to only return the children of the found parent Partner. An example would be:

=SUMIF(CHILDREN([Week Number]4, >0)

This would sum all of the child rows of the column Week Number at the cell I found (4), as long as the number was greater than 0. I think you should be able to modify this to get where you're going.

Stephanie Lapera

I can't use the Children function because I am calculating on a different sheet than where the data originates.

Paul Newcome

You are going to need a SUMIFS (with the "S" on the end) to incorporate more than one range/criteria set.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.