Counting specific values in multi select cells

Hi All,

I am trying to count how many offices have certain engagements (choices are Calls, Email, Weekly PF call, FB gorup, Other, Texts). These cells are linked cells from another sheet.

I tried these formulas:

=countif([Office Engagement:Office Engagement], contains("Calls", Office Engagement@row))

=countif([Office Engagement:Office Engagement], find("Calls", Office Engagement@row))

=countif([Office Engagement:Office Engagement], has(Office Engagement@row, "Calls))

and they are pulling 0 even though the choice is in the cell. I've tried to add "> 0" at the end of the countif formula, but then it only pulls how many cells total that are not blank.

Please advise.

Thank you!

Find more posts tagged with

Formulas

Accepted answers

Andrée Starå

@bea.sun

No worries!

Happy to help!

Try something like this.

=COUNTIF([Office Engagement]@row, CONTAINS("Calls", @cell))

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

Andrée Starå

Hi @bea.sun

I hope you're well and safe!

Do you have one column named Office Engagement and another one names OfficeEngagement?

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

bea.sun

Hi Andree,

Sorry, there's only one column and its Office Engagement.

Thank you!

Andrée Starå

@bea.sun

No worries!

Happy to help!

Try something like this.

=COUNTIF([Office Engagement]@row, CONTAINS("Calls", @cell))

Did that work?

bea.sun

Yes, it did. But I want to count a range of cells, not just one cell. Is that possible?

bea.sun

I just tried it with a range of cells and it worked! I appreciate you, thank you!!

Andrée Starå

@bea.sun

Excellent!

You're more than welcome!

Mary Kirby

I am having a similar issue. I am trying to create a column on a new sheet that shows the team member count by project.

The Sheet that I am pulling from has a multi-select column as it is sometime necessary to have team members assigned to multiple projects due to schedules/rows.

I have tried multiple options, including the formula below

=COUNTIF([{Sterling QAQC Whiteboard 2.0 Range 1}]@row), CONTAINS("[Project]"@row)

Am I on the right track or should this perhaps be done via automation?

Thank you!

Genevieve P.

Hi @Mary Kirby

You're close!

With a multi-select column you'll want to use the HAS function to see if the cell has that selection.

It also looks like your references may be formatted a little incorrectly. For cross-sheet references, you only need the {reference} without the @row. The @row basically tells the formula to look in this row, but you want it to look in an entire column in another sheet.

Try this:

=COUNTIF({Sterling QAQC Whiteboard 2.0 Range 1}, HAS(@cell, Project@row))

Here are some Help Articles that you may find useful:

Cheers,

Genevieve

Mary Kirby

Perfect! Thanks a million!

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.