COUNTM formula with IF COUNTM Formula
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))))
Answers
-
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"))
-
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.
-
You have a typo here:
HAS(@cell, "FY24))
There should be a closing quote after the 4.
-
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.
-
It is currently: HAS(@cell, "FY24)
But should be: HAS(@cell, "FY24")
-
Updated and I get 0. should be 1.
-
I also tried it on the 2nd "CPS". that should be 8, but I get 0 as well.
-
Is the exact value of the cell CPS or CE, or is this only part of the text string?
-
the column is a drop-down menu, so is the other. that's why I was using COUNTM for both to begin with.
-
Good Morning, BTW.
-
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)
-
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.
-
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))
-
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.
-
COUNTM just looks at the number of selections/entries in a given range. It does not take into account any criteria.
Have you double checked your references to ensure they are pointing at the correct ranges? I have this working across sheets.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!