Help with COUNTIFS function with external references where first variable has multiple choices

22Wordsmith

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.

KPH

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")

https://help.smartsheet.com/function/has

KPH

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")

https://help.smartsheet.com/function/has

22Wordsmith

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

KPH

Happy to help, @22Wordsmith

22Wordsmith

@KPH I have a follow-up question, please.

I have a drop-down 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.

KPH

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.

22Wordsmith

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!

KPH

I’m pleased that all made sense.

Thanks for the nomination 🦸🏻‍♀️