SUMIFS & CONTAINS formula

Emily G.

Hi,

I'm trying to identify the # of request types by a specific job title. The source of the data is on another sheet. The formulas I have tried so far have been producing zero as a result (which I know is incorrect based on the source data).

=SUMIFS({Request Type}, {Request Type}, ="Initiative / Project", {BranchJF}, CONTAINS(OR(@cell = "All Colleagues", @cell = "Branch Managers"), {BranchJF}))

=SUMIFS({Request Type}, {BranchJF}, CONTAINS(OR(@cell = "All Colleagues", @cell = "Branch Managers"), {BranchJF}), {Request Type}, ="Training")

Note: All Colleagues needs to be included in the formula, it means the request type applies to all job titles.

Lucas Rayala

https://community.smartsheet.com/discussion/comment/364561#Comment_364561

Hi @Emily G. this is closer -- the SUM wouldn't work unless the request type is a series of numbers that can be added. Try this:

=COUNTIFS({Request Type}, "Initiative / Project", {BranchJF}, OR(CONTAINS("All Colleagues", @cell), CONTAINS("All Managers", @cell), CONTAINS("Assistant Branch Managers",@cell)))

And, just editing, if these are the exact values, you don't need the CONTAINS, you can just do:

=COUNTIFS({Request Type}, "Initiative / Project", {BranchJF}, OR(@cell="All Colleagues", @cell="All Managers", @cell="Assistant Branch Managers))

I like the contains, although I usually shorten them to partial word searches, such as CONTAINS("Colleag", @cell). It helps to avoid instances were people fat-finger entries.