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

Stephanie Lapera

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

(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!

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.