Counting specific values in multi select cells

bea.sun

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!

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!

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?

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

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!

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

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:

• HAS Function
• Create Efficient Formulas with @cell and @row
• Create cross sheet references to work with data in another sheet
• Create a Cell or Column Reference in a Formula

Cheers,

Genevieve

Mary Kirby

Perfect! Thanks a million!