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

Find more posts tagged with

Sheets

Formulas

Reports

Cell Reference Limits

logic

Accepted answers

All comments

Eric Law

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)

Henry L.

@Eric Law

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?

Eric Law

@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)

Henry L.

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

Eric Law

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

Henry L.

@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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.