Count 2 text fields in multi-select options

Hi there -

I'm trying to count if a row has any of of the selections "Briefs" or "All Communications", but not when the row only "Events" or is blank. The report pulls correctly when I choose "Any of" those two in the multi-select, but I want to write a formula in the summary so I can pull it onto a dashboard. I feel like it has to be possible with OR, or another way, but I cannot figure it out. Please help if you know how to write it. Below is the best I have but it's missing some data for "All Communications"

=COUNTIF([Email List]:[Email List], CONTAINS("Briefs", @cell))

Thank you!

Find more posts tagged with

Formulas

Accepted answers

Christian Graf

Hi @Leslie Murray,

The following formula should work if you want to exclude any entry that contains "Events":

=COUNTIFS([Email List]:[Email List], OR(FIND("Briefs", @cell) > 0, FIND("Communications", @cell) > 0, AND(FIND("Briefs", @cell) > 0, FIND("Communications", @cell) > 0)), [Email List]:[Email List], NOT(FIND("Events", @cell) > 0))

The following formula should work if you want to include any entry that has Briefs or communications along with events values:

=COUNTIFS([Email List]:[Email List], OR(FIND("Briefs", @cell) > 0, FIND("Communications", @cell) > 0, AND(FIND("Briefs", @cell) > 0, FIND("Communications", @cell) > 0)))

Hope this helps!

All comments

Christian Graf

Hey there @Leslie Murray ,

Are the values "Breifs" and "All Communications" found in the same column? or seperate columns?

If in the same column try:

=COUNTIFS([Type Column]:[Type Column], ="Briefs", [Type Column]:[Type Column], ="All Communications")

If that doesn't solve your problem, please consider sharing a screenshot of the sheet without any sensitive information and I could tailor an exact formula for your needs.

Hope that helps! Thanks

Leslie Murray

Hi @Christian Graf -

Thanks for helping!

Unfortunately that lead to 0 results... This is the formula that I used

=COUNTIFS([Email List]:[Email List], ="SCCEI China Briefs", [Email List]:[Email List], ="All SCCEI Communications")

Here is a screenshot of the part of the column.

Thanks!

Screen Shot 2022-05-24 at 1.52.05 PM.png

Christian Graf

Hi @Leslie Murray,

The following formula should work if you want to exclude any entry that contains "Events":

The following formula should work if you want to include any entry that has Briefs or communications along with events values:

=COUNTIFS([Email List]:[Email List], OR(FIND("Briefs", @cell) > 0, FIND("Communications", @cell) > 0, AND(FIND("Briefs", @cell) > 0, FIND("Communications", @cell) > 0)))

Hope this helps!

Leslie Murray

Amazing! Thank you, @Christian Graf . The second option was exactly what I was trying to get to! Very much appreciate 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.