How to check if cell contains one thing or another?
Hi all,
I'm making a sheet that references another sheet to get data for a detailed graph. I'm trying to count the total number of small, medium, and large projects. The formula I have that works for individual campuses is as follows: =COUNTIFS({Size}, CONTAINS("Small", @cell), {Campus}, CONTAINS("Example", @cell), {In Progress}, 1)
My problem is that I have one field where we combine two campuses that track data separately. I'm pretty certain that all of the data are referenced correctly. If I could get help reworking the formula below to account for this that would be great.
=COUNTIFS({Size}, CONTAINS("Small", @cell), {Campus}, CONTAINS(OR(@cell = "North", @cell = "South"), @cell), {In Progress}, 1
Thanks,
Henry
Answers
-
Hello @Henry L. it seems like you have an extra @cell.
=COUNTIFS({Size}, CONTAINS("Small", @cell), {Campus}, CONTAINS(OR(@cell = "North", @cell = "South"), @cell), {In Progress}, 1
Try =COUNTIFS({Size}, CONTAINS("Small", @cell), {Campus}, CONTAINS(OR(@cell = "North", @cell = "South"), {In Progress}, 1)
-
Unfortunately that just gives me an #INCORRECT ARGUMENT SET error. You'd think it would work as {Campus} is the range and the CONTAINS(OR(... is the CRITERION. Could it be because, some how, @cell = "North" or @cell = "South" are not resolving to a Boolean value?
-
@Henry L. Try replacing CONTAINS(OR(@cell = "North", @cell = "South") with OR(CONTAINS("North",@cell), CONTAINS("South", @cell), so the formula will be
=COUNTIFS({Size}, CONTAINS("Small", @cell), {Campus}, OR(CONTAINS("North",@cell), CONTAINS("South", @cell), {In Progress}, 1)
-
@Eric Law I had tried that as well, I know the OR operator can be finicky with order. For that I just get #INVALID DATA TYPE. Do you think I would be able to say something like: if campus contains north at the cell and then a new range and argument if campus contains south at the cell? That might just be working like an and which isn't what I want as there aren't any cells will multiple campuses listed.
-
@Henry L. Got it... I think. We were missing a parenthesis. Try it again. 🤞
=COUNTIFS({Size}, CONTAINS("Small", @cell), {Campus}, OR(CONTAINS("North",@cell), CONTAINS("South", @cell)), {In Progress}, 1)
If this doesn't work, have you tried removing the Campus part and see if it works? Also, try to work the Campus part by itself.
-
@Eric Law I don't get an error this time, but still no dice. I will trouble shoot a bit an chat with some more people...maybe I need a different approach. Regardless, thanks for all the help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!