SUMIFS with AND COONTAINS
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.
Best Answer

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.
Answers

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.

Thank you Matt, this has worked.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!