Using SUMIF with FIND criteria

dvrgrl
dvrgrl
edited 12/09/19 in Formulas and Functions

Hi I've searched the community and can't seem to find an answer for this one.

 

I have a column with project roles and the hours for each role required for individual tasks.

I would like to summarize the total hours for each role

Currently I am creating hidden columns for each role and to determine the role hours for each task. I'm calculating using this formula. This is fine for a couple roles but I would like to it to be more dynamic.

=IF(FIND("GM", $Role6) > 0, $HRperFTE6, 0)

GM is a role

Role is the column that has the roles required for a task

HRperFTE are the hours each role identified in Role will spend on the tasks.

 

I am then summarizing using a simple sum:

=SUM($[GM Hrs]6:$[GM Hrs]16)

I would like to calculate using a SUMIF. I thought this would be the formula

=SUMIF(Role:Role, FIND("GM", Role@row) > 0, HRperFTE:HRperFTE)

but that returns the sum of all values in the HRperFTE range.

I feel like I'm missing something simple and I'm hoping someone might be able to help me here.

Any assistance is appreciated.

Thanks!

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!