How to avoid counting a drop down option?

jehup

I have one drop down option which is used on 3 cells, 2 cells have "EVT" and "OK2Fab" and 1 cell "EVT" "OK2Fab" "OK2Tool", when I use: COUNTIF({WHS Hot Issue Tracker_NO Range 3}, AND(HAS(@cell, "OK2Fab"), HAS(@cell, "EVT"))) It counts 3 and when I use COUNTIF({WHS Hot Issue Tracker_NO Range 3}, AND(HAS(@cell, "EVT"), HAS(@cell, "OK2Fab"), AND(HAS(@cell, "OK2Tool")))) it counts 2. So EVT OK2Fab = 3 which is incorrect (should be 2) as I want only the two options to be counted for. How can I just count EVT and OK2Fab? and remove the option with OK2Tool?

Kelly Moore

Hey @jehup

You should be able to include the COUNTM() function as criteria if you're trying to limit other responses.

=COUNTIFS({WHS Hot Issue Tracker_NO Range 3}, (HAS(@cell, "OK2Fab"), HAS(@cell, "EVT"), COUNTM(@cell)=2))

Does this work for you?

Kelly

jehup

@Kelly Moore Thanks. No is not parsing. Seems like there is an error in the formula.

Kelly Moore

Hey @jehup

=COUNTIFS({WHS Hot Issue Tracker_NO Range 3}, AND((HAS(@cell, "OK2Fab"), HAS(@cell, "EVT"), COUNTM(@cell)=2)))

it's missing the AND

Kelly