SUMIFS with AND COONTAINS

ibimon

Hi.

I'm trying to do SUMIFS with AND CONTAINS.

The logic is SUM entry from column A(referenced from another page), if Column B(referenced from another page) contains "Bryan" (must bring any hits that have Bryan in it, what comes after Bryan doesn't matter) AND Column C(referenced from another page) contains "Operations" (same logic as with Bryan).

currently have to use bellow and write the exact name and year, but the problem is that the year will change and our smartsheet will miss any data that will be Bryan John 2024 for example or not Operations Manager but Operations Assistant.

=SUMIFS({monthly tally Range 3}, {MCD monthly tally Range 1}, "Bryan John 2025", {MCD monthly tally Range 2}, "Operations Manager")

I've used AND(CONTAINS before but it doesn't seem to work when referencing another sheet, and probably I'm not inputting it correctly.

Any help appreciated.

Mike TV

I'm not 100% sure if this is what you're trying to do without an example screenshot of your underlying sheet(s). Here's what I came up with. Example sheet:

Formula:

=SUMIFS([Sum Column]:[Sum Column], Name:Name, CONTAINS("Bryan", @cell), Department:Department, CONTAINS("Operations", @cell))

Notice it didn't sum Bryan in Accounting. Just the Bryan's in the different types of Operations.

Mike TV

I'm not 100% sure if this is what you're trying to do without an example screenshot of your underlying sheet(s). Here's what I came up with. Example sheet:

Formula:

=SUMIFS([Sum Column]:[Sum Column], Name:Name, CONTAINS("Bryan", @cell), Department:Department, CONTAINS("Operations", @cell))

Notice it didn't sum Bryan in Accounting. Just the Bryan's in the different types of Operations.

ibimon

Thank you Matt, this has worked.