SUMIF formula when cell contains multiple collaborators

Melissa Logan

I currently have a formula that will sum up the "Hours Used" from the "proposed hours needed" field based on which intern is assigned.

=SUMIF(Intern:Intern, "Michelle", [Proposed Hours Needed]:[Proposed Hours Needed])

However, when BOTH interns are assigned, it doesn't acknowledge the hours - how can I update my formula to include that if both interns are chosen to divide the Proposed hours in half and add the halves independently to each interns Hours Used? - so in the example, I'd want 10 hours to show up for each intern in their Hours Used

Melissa Logan

ok - so actually, one of the rows got pushed to the bottom, so I missed 40 hours! the formula DOES work, except that it's not splitting the hours, it's adding the full amount to both parties - when both parties are present, I need it to divide the total number by half and add that to each person's hours used individually - so instead of 20 hours each for the project, it's 10 hours each

Paul Newcome

I am going to suggest a "helper" column where you can establish how many hours goes to an individual. The formula for this would look something along the lines of...

=[Proposed Hours Needed]@row / COUNTM(Intern@row)

Then you would run your SUMIFS on the helper column.

Paul Newcome

Try...

=SUMIF(Intern:Intern, FIND("Michelle", @cell) > 0, [Proposed Hours Needed]:[Proposed Hours Needed])

Melissa Logan

hmm, didn't work - kept coming out to a higher number - there is also an "intern" labeled as "open" which just means it hasn't been assigned to either one of them, and although it doesn't seem to be pulling in those numbers either, I don't know if that affects the formula?

Paul Newcome

Can you provide a screenshot of the source data? What is the formula outputting?

Melissa Logan

ok - so actually, one of the rows got pushed to the bottom, so I missed 40 hours! the formula DOES work, except that it's not splitting the hours, it's adding the full amount to both parties - when both parties are present, I need it to divide the total number by half and add that to each person's hours used individually - so instead of 20 hours each for the project, it's 10 hours each

Paul Newcome

I am going to suggest a "helper" column where you can establish how many hours goes to an individual. The formula for this would look something along the lines of...

=[Proposed Hours Needed]@row / COUNTM(Intern@row)

Then you would run your SUMIFS on the helper column.

Melissa Logan

Perfect! thank you so much!