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.
JH
Best Answer
-
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
Answers
-
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
-
Worked perfectly. Thank you.
-
Hello, @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
Help Article Resources
Categories
Check out the Formula Handbook template!