Welcome!

COUNTIF vs COUNTIF HAS

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.

Find more posts tagged with

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Week Number Year to Year
I've got the following formula in a Check Box column to check when something is due in the Next 3 weeks. =IFERROR(IF(AND(WEEKNUMBER([Projected Cleaning Date]@row ) = WEEKNUMBER(TODAY()) + 3, YEAR([Projected Cleaning Date]@row ) = YEAR(TODAY())), 1), "") I have them for 2 weeks, 3 weeks, 4 weeks, and 5 weeks. These stopped…
Use Sheet Summary as cell reference for a different sheet
I'm using salesforce connector to pull my team's hours information in real-time. The Salesforce connector sheet contains sheet summaries that I'd like to use a cell reference for a different sheet. I can't seem to find the best way or formula to do this. I don't want to use a dashboard with report widgets because I prefer…
Issues with IF formula
I have two formulas which work well independently, but when I combine them they don't. formula 1: =IF(YEAR([Joined date]@row ) = 2025, JOIN(COLLECT({Membership Survey 2025 - Experience}, {Membership Prioritisation Survey 2025 - Org}, [Organisation name]@row ))) formula 2: =IF(YEAR([Joined date]@row ) < 2025,…