SUMIFS & CONTAINS formula

Options

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.

Tags:

Best Answer

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    edited 02/28/23 Answer ✓
    Options

    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.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!