HAS with multiple criteria and qualifying checbox

Lily DV

I'm trying to pull data related to only 1 program and not another, then add in whether a check box is ticked. The first part of my formula works:

=COUNTIFS(Program:Program, OR(HAS(@cell, "ECSP & HCW"), HAS(@cell, "ECSP")))

But when I add the second part it becomes unparseable:

=COUNTIFS(Program:Program, OR(HAS(@cell, "ECSP & HCW"), HAS(@cell, "ECSP"))), ([External providers and advocacy groups]:[External providers and advocacy groups], 1))

How can I get this to work so I can see the checkboxes only from the criteria indicated in the initial column?

jmyzk_cloudsmart_jp

Hi @Lily DV

You have unnecessary parentheses. So, when I removed those, the formula worked.

Your formula

=COUNTIFS(Program:Program, OR(HAS(@cell, "ECSP & HCW"), HAS(@cell, "ECSP"))), ([External providers and advocacy groups]:[External providers and advocacy groups], 1))

My formula

=COUNTIFS(Program:Program, OR(HAS(@cell, "ECSP & HCW"), HAS(@cell, "ECSP")), [External providers and advocacy groups]:[External providers and advocacy groups], 1)

In your formula, the COUNTIFS has OR() as a condition. But as you have an extra ")" , the formula considers COUNTIFS finish there.

Then, the COUNTIFS expect this structure, COUNTIFS(range1, condition1, range2, condition2). But, as you enclose " [External providers and advocacy groups]:[External providers and advocacy groups], 1)" in parentheses, the formula sees this structure, "COUNTIFS(range1, condition1, (range2, condition2))" and considers that you do not provide the condition2. ((range 2, condition2) becomes range2 with the parentheses.)

I am not sure what you mean by "ECSP & HCW". If you mean to select both ECSP and HCW in the multiple dropdown list, the formula should be the following.

=COUNTIFS(Program:Program, OR(AND(HAS(@cell, "ECSP"), HAS(@cell, "HCW")), HAS(@cell, "ECSP")), [External providers and advocacy groups]:[External providers and advocacy groups], 1)

The demo sheet below is editable, so please test how the formula works.

https://app.smartsheet.com/b/publish?EQBCT=81d6f7c526b54694b6809ed079ce9add

jmyzk_cloudsmart_jp

Hi @Lily DV

You have unnecessary parentheses. So, when I removed those, the formula worked.

Your formula

=COUNTIFS(Program:Program, OR(HAS(@cell, "ECSP & HCW"), HAS(@cell, "ECSP"))), ([External providers and advocacy groups]:[External providers and advocacy groups], 1))

My formula

=COUNTIFS(Program:Program, OR(HAS(@cell, "ECSP & HCW"), HAS(@cell, "ECSP")), [External providers and advocacy groups]:[External providers and advocacy groups], 1)

In your formula, the COUNTIFS has OR() as a condition. But as you have an extra ")" , the formula considers COUNTIFS finish there.

Then, the COUNTIFS expect this structure, COUNTIFS(range1, condition1, range2, condition2). But, as you enclose " [External providers and advocacy groups]:[External providers and advocacy groups], 1)" in parentheses, the formula sees this structure, "COUNTIFS(range1, condition1, (range2, condition2))" and considers that you do not provide the condition2. ((range 2, condition2) becomes range2 with the parentheses.)

I am not sure what you mean by "ECSP & HCW". If you mean to select both ECSP and HCW in the multiple dropdown list, the formula should be the following.

=COUNTIFS(Program:Program, OR(AND(HAS(@cell, "ECSP"), HAS(@cell, "HCW")), HAS(@cell, "ECSP")), [External providers and advocacy groups]:[External providers and advocacy groups], 1)

The demo sheet below is editable, so please test how the formula works.

https://app.smartsheet.com/b/publish?EQBCT=81d6f7c526b54694b6809ed079ce9add

PeggyLang

@jmyzk_cloudsmart_jp

In the above scenario if you had a second multi-select column for instance State (where program was available) could you pull (countifs) 'Program' HAS "ECSP" and 'State' HAS "FL"?

jmyzk_cloudsmart_jp

Hi @PeggyLang

I added a helper column, the "Has FL" checkbox column, and used that as another condition of the COUNTIF function.

[Has FL] =IF(HAS(State@row, "FL"), 1, 0)

[Count State = FL 1] =COUNTIFS(Program:Program, OR(HAS(@cell, "ECSP & HCW"), HAS(@cell, "ECSP")), [External providers and advocacy groups]:[External providers and advocacy groups], 1, [Has FL]:[Has FL], 1)

https://app.smartsheet.com/b/publish?EQBCT=81d6f7c526b54694b6809ed079ce9add&_gl=1*1sg1oyt*_ga*MTU3MzYxODczMS4xNjQyMDMyNTA1*_ga_ZYH7XNXMZK*MTcwNzUyMTY2Ni4yMDYuMS4xNzA3NTIxNjg3LjM5LjAuMA..&_ga=2.37401124.523152569.1707439044-1573618731.1642032505