COUNTIFS with COLLECT?

I am trying to use the COLLECT function, wihtin a COUNTIFS, to count cells that contain numbers and fall within range of percentages. I feel I am close in that I get an Incorrect Arguement error rather than Unparseable. Any advice?
=COUNTIFS(COLLECT({CP Complete 1}, ISNUMBER(@cell), >=0.9, <=0.8, {Go Live}, >=DATE(2021, 4, 1), {Go Live}, <=DATE(2021, 6, 30)))
CP Complete 1- Percent column, overall completness of critical path
Go Live - Date column, using to filter these items by quarter
Thanks!
Best Answers
-
My apologies! I just realized that the greater than and less than symbols are the opposite way around.
You'll want to say less than 0.99 and greater than 0.95. Currently your formula says greater than 0.99 and less than 0.95, which is why it's returning 0.
Try this:
=COUNTIFS({CP Complete 1}, <=0.99, {CP Complete 1}, >=0.95, {Go Live}, >=DATE(2021, 4, 1), {Go Live}, <=DATE(2021, 6, 30))
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Oh goodness--let's hear it for math dyslexia, my life long enemy! Thanks for your second set of eyes--it's working now!
Answers
-
You are close! However since you're using COUNTIFS (with an S), you actually don't need to COLLECT function (it's redundant). You will also need to reiterate the column/range every time you have a criteria, such as the "greater than 0.9" and "less than 0.8". Each of these will need the range listed.
Try this:
=COUNTIFS({CP Complete 1}, >=0.9, {CP Complete 1}, <=0.8, {Go Live}, >=DATE(2021, 4, 1), {Go Live}, <=DATE(2021, 6, 30))
If this doesn't work, it would be helpful to see screen captures (but please block out sensitive data) and a detailed explanation of what you want the formula to do!
Cheers,
Genevieve
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Thank you so much Genevieve--it doesn't choke now, but it doesn't appear to return the correct numbers. If I filter the data with he same criteria, I will get numbers but the formula returns a zero.
=COUNTIFS({CP Complete 1}, >=0.99, {CP Complete 1}, <=0.95, {Go Live}, >=DATE(2021, 4, 1), {Go Live}, <=DATE(2021, 6, 30))
I'm stumped what I am missing; the other 2 ranges are in fact zero. I am fixing the 100% as I didn't have the quarterly filter on that one.
-
My apologies! I just realized that the greater than and less than symbols are the opposite way around.
You'll want to say less than 0.99 and greater than 0.95. Currently your formula says greater than 0.99 and less than 0.95, which is why it's returning 0.
Try this:
=COUNTIFS({CP Complete 1}, <=0.99, {CP Complete 1}, >=0.95, {Go Live}, >=DATE(2021, 4, 1), {Go Live}, <=DATE(2021, 6, 30))
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
Oh goodness--let's hear it for math dyslexia, my life long enemy! Thanks for your second set of eyes--it's working now!
-
No problem at all! I missed it, too. I'm glad to hear it's working now!
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!