Can you add in criteria that uses contains in a SUMIFS formula?

Options

Right now here is my current formula: =SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, "Victoria", {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28)). Works great, however, I have the formula so it is pulling in the hours associated with "Victoria". Victoria is listed in the "owner column", which sometimes a cell in the owner column has more than one person in it, which then does not pull in the hours. Is there a way to have the formula pull in the hours even if there is another owner in the cell?

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this

     =SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, CONTAINS("Victoria", @cell), {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28))

    What I didn't ask in your other post was if the column you were searching was a contact column, or just a name in a cell. CONTAINS does not work in a contact column, but FIND does. FIND however, returns a number, not true or false. We must then look for a value >0 to determine if something was actually found.

    =SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, FIND("Victoria", @cell)>0, {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28))

    cheers

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Try this

     =SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, CONTAINS("Victoria", @cell), {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28))

    What I didn't ask in your other post was if the column you were searching was a contact column, or just a name in a cell. CONTAINS does not work in a contact column, but FIND does. FIND however, returns a number, not true or false. We must then look for a value >0 to determine if something was actually found.

    =SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, FIND("Victoria", @cell)>0, {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28))

    cheers

    Kelly

  • This worked! Thank you SO much for your help on this!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!