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

Since my typed graph didn't show up well

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.

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

You are going to need a SUMIFS (with the "S" on the end) to incorporate more than one range/criteria set.
Help Article Resources
Categories
Check out the Formula Handbook template!