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

Victoria Scholly

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?

Kelly Moore

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

Kelly Moore

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

Victoria Scholly

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