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

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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
    Tina Rustvold ✭✭✭✭✭
    Answer ✓

    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

  • Genevieve P.
    Genevieve P. Employee Admin

    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
    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.
    Genevieve P. Employee Admin
    Answer ✓

    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
    Tina Rustvold ✭✭✭✭✭
    Answer ✓

    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.
    Genevieve P. Employee Admin

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!