Count 2 text fields in multi-select options

Leslie Murray · 05/24/22

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!

Christian Graf · 05/24/22

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!

Christian Graf · 05/24/22

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 · 05/24/22

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 · 05/24/22

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!

Leslie Murray · 05/24/22

Amazing! Thank you, @Christian Graf . The second option was exactly what I was trying to get to! Very much appreciate the help!

Count 2 text fields in multi-select options

Best Answer

Answers

Categories