COUNTIF vs COUNTIF HAS

Graham Staniforth

I have the simple COUNTIF formula in a roll up sheet but am struggling with the formula picking up a single entry from a multiple dropdown selection:

The simple count if formula that is working is:

=COUNTIF({Sprint Tracker Range 5}, "Subject")

The more complex COUNTIF with HAS I have created is that is not working is:

=COUNTIF({Sprint Tracker Range 5}, HAS({Sprint Tracker Range 5}, "Subject"))

The problem is the HAS formula is pulling the wrong total of appearances (pulling zero).

Any advice is greatly appreciated.

Graham Staniforth

solved:

=COUNTIF({Sprint Tracker Range 5}, HAS(@cell, "Subject"))

Andrée Starå

Hi Graham,

Glad you solved it!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Please help the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Chris Barushak (Batesville)

I’m struggling to get this function to work properly – ‘COUNTIF’ with ‘HAS’. I have a Dropdown (Multi-Select) column with names from one of our teams. I’m trying to be able to count # of times their specific names show up in the column.

 

• So I started with this: =COUNTIF([EMT Member]:[EMT Member], HAS([EMT Member]:[EMT Member], "Johnny P")) -- this gives an answer of 0, but it should give an answer of 5.
• After reviewing the help articles and SS Community – it suggested to replace the Search Range inside the HAS function with “@Cell”
• So then I tried this: =COUNTIF([EMT Member]:[EMT Member], HAS(“@Cell”, "Johnny P")) à This also gives an answer of 0, but it should give an answer of 5. If I drop the quotes around @Cell, then I get an #UNPARSEABLE error.

 

Sheet link is: https://app.smartsheet.com/sheets/FmXg7PQRVJcfqM9VfqFrFJqXgcXv3R3vHR5PHXh1

 

Getting this figured out would really help in a lot of ways, I have lots of uses for this across numerous sheets. 

 

Andrée Starå

Hi @Chris Barushak (Batesville)

I hope you're well and safe!

Try something like this.

=COUNTIFS([EMT Member]:[EMT Member], HAS(@cell, "Johnny P"))

Did that work/help?

I hope that helps!

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 or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!