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!

Tags:

Best Answer

  • Christian Graf
    Christian Graf ✭✭✭✭
    Answer ✓

    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!

Answers

  • Christian Graf
    Christian Graf ✭✭✭✭
    edited 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
    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!


  • Christian Graf
    Christian Graf ✭✭✭✭
    Answer ✓

    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
    Leslie Murray ✭✭✭✭✭

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