# 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!

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭

Oh goodness--let's hear it for math dyslexia, my life long enemy! Thanks for your second set of eyes--it's working now!

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭

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.

• Employee

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭

Oh goodness--let's hear it for math dyslexia, my life long enemy! Thanks for your second set of eyes--it's working now!

• Employee

No problem at all! I missed it, too. I'm glad to hear it's working now!