SUMIF formula help

This discussion was created from comments split from: Need support using wildcard & sumif function.

Answers

  • Hi

    Hoping someone can help!

    As we don't have the Resource Management add on I'm trying to calculate the number of days assigned to each person in the project plan, however some "Assigned To" cells contains multiple names.

    I have tried to use the above formula provide by Parul Mishra

    =SUMIF({PROJECT PLAN: CERIDIAN DAYFORCE Range 2}, CONTAINS("Jack Jones", @cell), {PROJECT PLAN: CERIDIAN DAYFORCE Range 3})

    (Range 2 is "Assigned To" column, Range 3 is "Duration" column)

    But the formula is either returning 0 or 1, even though I know that Jack Jones has multiple days.

    Any help would be much appreciated.

    Many thanks


    JW

    (PS not sure what the @cell part of the CONTAINS formula is referring to)

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @Jacqué Whitlock

    To answer the @cell question. This tells the function 'CONTAINS' to look at each cell, one by one, in your range {PROJECT PLAN: CERIDIAN DAYFORCE Range 2} and see if it contains "Jack Jones".

    When using a multiselect column, you have three functions that can help see the individual responses within the cell. These are CONTAINS, HAS and FIND.

    Try this

    =SUMIF({PROJECT PLAN: CERIDIAN DAYFORCE Range 2}, HAS(@cell,"Jack Jones"), {PROJECT PLAN: CERIDIAN DAYFORCE Range 3})

    Does this work for you?

    Kelly

  • That work's perfectly thank you Kelly, very much appreciated 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!