Help with COUNTIFS function with external references where first variable has multiple choices
Hi,
I have a sheet with ranges identified as SUBJECTM and FLAGM. On my summary page, I have a list of all variables, with the column identified as Metric. In the main sheet, the subject field is a series of checkboxes with the ability to select multiple options (think : maths, english, french, art).
I want to count the instances of maths where the flag is red.
I've used the formula =COUNTIFS({SubjectM},(Metric@row),{FlagM},"Red") where the entry in Metric is Maths.
It is returning a result of 0 but I know that there is at least 1 maths entry flagged red.
Do I need a COUNTM for the first variable and if so, how do I integrate it with the COUNTIFS formula, please? I tried to write it as = COUNTIFS(COUNTM(...) but it came back as computer says no.
Best Answer

COUNTM counts the number of things in a range. So if a cell contains English and Math the COUNTM would be 2. I don’t think that is what you want.
It sounds like your SubjectM column could be a multi select dropdown. If so, the issue is that a cell with English and Maths checked will not equal a cell with just Math in it (in the metric@row cell). You need to incorporate a HAS function.
The formula would be something like
=COUNTIFS({SubjectM},HAS(@cell,Metric@row),{FlagM},"Red")
Answers

COUNTM counts the number of things in a range. So if a cell contains English and Math the COUNTM would be 2. I don’t think that is what you want.
It sounds like your SubjectM column could be a multi select dropdown. If so, the issue is that a cell with English and Maths checked will not equal a cell with just Math in it (in the metric@row cell). You need to incorporate a HAS function.
The formula would be something like
=COUNTIFS({SubjectM},HAS(@cell,Metric@row),{FlagM},"Red")

Greatly appreciate the quick assistance with this. I love this community.

Happy to help, @22Wordsmith

@KPH I have a followup question, please.
I have a dropdown list of names. At the moment we aren't deleting any entries but have added DUPLICATE  NFA to our list of names (for the obvious purpose of flagging duplicates). My total entries count was a simple count of the reference numbers but now I need to exclude anything with the name DUPLICATE  NFA.
Would I do this as =COUNT({CountM}) UNLESS({NameM},HAS,@cell,"DUPLICATE  NFA"))
CountM is an external reference to the Reference column and NameM is the external reference to the name list.

Not exactly @22Wordsmith
Your HAS syntax is a little wonky and there is no such function as UNLESS in smartsheet.
If the list of names is not multiselect, you don't need HAS. You could use COUNTIF again but with a <> for doesn't equal. So instead of COUNT UNLESS you would use COUNTIF does not equal.
=COUNTIF({NameM}, <>"DUPLICATE  NFA")
Would give you a count of rows with Names in NameM so long as that name is not DUPLICATE  NFA
=COUNTIFS({CountM}, <>"", {NameM}, <>"DUPLICATE  NFA")
Would give you the count of rows where the reference column is not blank and the Names in NameM is not DUPLICATE  NFA
If names is multiselect, you can use a NOT and HAS.
=COUNTIFS({CountM}, <>"", {NameM}, NOT(HAS(@cell, "DUPLICATE  NFA")))
Just be aware this will not count any rows that have DUPLICATE  NFA in the name column, even if there is another name in there as well.

1000 thanks. I really appreciate the detailed explanation of syntax. The name column is not multiselect so I can go with the =COUNTIF({NameM}, <>"DUPLICATE  NFA") option.
Please accept one Hero of the Afternoon nomination!

I’m pleased that all made sense.
Thanks for the nomination 🦸🏻♀️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!