SUMIF formula when cell contains multiple collaborators
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
Best Answers

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

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.
Answers

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

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?

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

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

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.

Perfect! thank you so much!
Help Article Resources
Categories
Check out the Formula Handbook template!