COUNTM formula with IF COUNTM Formula

Paula Katzenberg

Help,

I've tried multiple formulas mentioned in various question strings and nothing seems to work. (Preface: I haven't used formulas in quite a while). I have a sheet with two different drop down columns that I need to reference in the formula to get a count based on certain criteria. In the end I want to count the number of times "CE" appears for a FY dropdown menu for FY24. Your help is appreciated. This was the last formula I tried that I thought would work: =COUNTM((({Completed/Canceled Projects Range 3}, HAS(@cell, "CE"), IF (COUNTM({Completed/Canceled Projects Range 2}, HAS(@cell, "FY24))))

Carson Penticuff

If I understand your use case correctly, you would not use the COUNTM function. COUNTM is for counting how many options there are in a dropdown. For this situation, COUNTIFS would be the correct option. Give this a try:

=COUNTIFS({Completed/Canceled Projects Range 3}, HAS(@cell, "CE"), {Completed/Canceled Projects Range 2}, HAS(@cell, "FY24"))

Paula Katzenberg

I did try all the COUNTIFS as well, including the one you mention above. =COUNTIFS({Completed/Canceled Projects Range 3}, HAS(@cell, "CE"), {Completed/Canceled Projects Range 2}, HAS(@cell, "FY24)) and I still get: #UNPARSEABLE.

The count for CE should be 1. There are other counts for that same formula for each dropdown option. It would only mean changing the CE once I got the formula to work.

Carson Penticuff

You have a typo here:

HAS(@cell, "FY24))

There should be a closing quote after the 4.

Paula Katzenberg

do you mean HAS(@cell, "FY24))) ? I've tried it with 2 and three closing quotes: and just for kicks just 1. sorry if I am misunderstanding.

Carson Penticuff

It is currently: HAS(@cell, "FY24)

But should be: HAS(@cell, "FY24")

Paula Katzenberg

Updated and I get 0. should be 1.

Paula Katzenberg

I also tried it on the 2nd "CPS". that should be 8, but I get 0 as well.

Carson Penticuff

Is the exact value of the cell CPS or CE, or is this only part of the text string?

Paula Katzenberg

the column is a drop-down menu, so is the other. that's why I was using COUNTM for both to begin with.

Paula Katzenberg

Good Morning, BTW.

Carson Penticuff

I have a slightly modified version of this (all on the same sheet) working in a test sheet.

Is it possible to share a screenshot of your layout? (Sensitive information hidden of course)

Paula Katzenberg

Happy to provide anything I can. I have a separate sheet with formulas.

The formula is only based within 1 sheet using 2 different drop down columns. That area is the 3 image shown.

1st is the Staus which the drop down menu shown. this will grow with time, FY25 etc…

2nd is the Resource column and its drop down. That shouldn't change with time like the other.

Carson Penticuff

Okay, I see the problem now. HAS searches for an exact match, so it will not see "FY24" in "Completed FY24". For this case, we can use CONTAINS. See if this works better:

=COUNTIFS({Completed/Canceled Projects Range 3}, CONTAINS("FY24", @cell), {Completed/Canceled Projects Range 2}, CONTAINS("CE", @cell))

Paula Katzenberg

I copy and pasted and still got 0 when it should be 1 for CE.

I can use the full 'Completed FY24'. that is not a problem. Also I tried the 2nd row using 'CPS'. that count should be 8 and it came out zero as well.

I really thought it should be COUNTM.

I did try: =COUNTM({Completed/Canceled Projects Range 2}, CONTAINS("Completed FY24", @cell), {Completed/Canceled Projects Range 3}, CONTAINS("CPS", @cell)) for the 2nd row, CPS, that count should be 8 and that formula returned 212.

Carson Penticuff

COUNTM just looks at the number of selections/entries in a given range. It does not take into account any criteria.

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

Have you double checked your references to ensure they are pointing at the correct ranges? I have this working across sheets.