COUNTIF with HAS formula

Hi,

I'm trying to use a COUNTIF formula with a HAS criteria, but it doesn't seem to be working for me. Would someone mind taking a look?

I'm trying to count the number of times "Apples" shows up in the multi-select column, Range A, while Range B also has "On Hold."

Here's the formula: =COUNTIFS({Range A}, HAS({Range A}, "Apples"), {Range B}, "On Hold")

Thank you.

Find more posts tagged with

Formulas

functionality

Accepted answers

Genevieve P.

Hi @jane.huynh90286

Try replacing the range within the HAS with @cell instead (read more about @cell by clicking here):

=COUNTIFS({Range A}, HAS(@cell, "Apples"), {Range B}, "On Hold")

If this doesn't work, can you let us know why? (Are you receiving an error, or an incorrect result?) Can you also confirm what column types the two different ranges are?

The HAS function is designed for multi-select drop-down columns and it requires an exact match. (Click here for more information).

Cheers!

Genevieve

All comments

Genevieve P.

Hi @jane.huynh90286

Try replacing the range within the HAS with @cell instead (read more about @cell by clicking here):

=COUNTIFS({Range A}, HAS(@cell, "Apples"), {Range B}, "On Hold")

If this doesn't work, can you let us know why? (Are you receiving an error, or an incorrect result?) Can you also confirm what column types the two different ranges are?

The HAS function is designed for multi-select drop-down columns and it requires an exact match. (Click here for more information).

Cheers!

Genevieve

jane.huynh90286

Worked perfectly. Thank you.

Chris Hallo

Hello, @Genevieve P. !

Genevieve P.

Hi @Chris Hallo

There are two things to adjust, here! First of all, it looks like you have an additional { in front of your reference.

Secondly, your HAS function is missing an element... the structure for HAS is as follows:

HAS(range, value)

In your case, the range is actually each individual cell in the previously stated column, so we'll use @cell

HAS(@cell,

Then you can list the value:

HAS(@cell, Population@row)

Try:

=COUNTIF({CaseTracker_PPS}, HAS(@cell, Population@row))

Cheers,

Genevieve

NBW501

Ok, I am struggling with this too…. any suggestions?

Genevieve P.

Hi @NBW501

In the HAS function, use @cell instead of the Pillar:Pillar range. You're looking for the word in each individual cell.

=COUNTIF(Pillar:Pillar, HAS(@cell, "Corporate Citizenship"))

Cheers,
Genevieve

NBW501

Thank You very much!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.