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

Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!