COUNTIFS with COLLECT?

Tina Rustvold

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!

Genevieve P.

Hi @Tina Rustvold

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

Tina Rustvold

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

Genevieve P.

Hi @Tina Rustvold

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

Tina Rustvold

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.

Genevieve P.

Hi @Tina Rustvold

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

Tina Rustvold

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

Genevieve P.

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