Using SUMIF with FIND criteria

12/10/18 Edited 12/09/19

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", [email protected]) > 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

  • Mike WildayMike Wilday ✭✭✭✭✭

    Can you share a screenshot to show us exactly what you're doing? Hide any sensitive information... but it looks at first glance like you're doing it right. There might be issues with how the find is working.

     

  • Did you try using @cell instead of [email protected]?

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

  • Mike WildayMike Wilday ✭✭✭✭✭

    @Brian - good idea. @row and @cell are buggers. Very useful. But sometimes don't produce results the same. 

  • edited 12/10/18

    Thanks Everyone

    I've attached a screenshot below. 

    I have tried using @cell where I have the @row but it comes up with #UNPARSEABLE

     

    The 68 is where I have the formula but it should equal 17

    =SUMIF(Role:Role, FIND("GM", [email protected]) > 0, HRperFTE:HRperFTE)

     

     

     

    SUMIF_FIND.PNG

  • Update!! 

    This was it, I thought I still had to use "[email protected]", but just "@cell" was it. 

    THANK YOU!!!!

  • Glad it worked! Cheers!

Sign In or Register to comment.