Null value for COUNTM COLLECT

Options

I am trying to return a null for any zero value for a chart but I get an incorrect argument set error when I try to add an IF statement. My original formula is a COUNTM(COLLECT) to count multiple entries from a multi-select dropdown that are NOT a certain value for several individuals for each month. How would I accomplish this? Greatly appreciate the help!

Working formula that returns 0:

=COUNTM(COLLECT({Issue}, {Review Date}, @cell <= DATE(2021, 1, 31), {Who Column}, CONTAINS("Smith", @cell), {Issue}, NOT(@cell = "N/A")))


Doesn't work:

=IF(COUNTM(COLLECT({Issue}, {Review Date}, @cell <= DATE(2021, 1, 31), {Who Column}, CONTAINS("Smith", @cell), {Issue}, NOT(@cell = "N/A")) >0, COUNTM(COLLECT({Issue}, {Review Date}, @cell <= DATE(2021, 1, 31), {Who Column}, CONTAINS("Smith", @cell), {Issue}, NOT(@cell = "N/A")))

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Heather Hall

    It looks like you're just missing a closing parenthesis after your initial statement, before the > 0

    This is the structure of the IF statement:

    IF(Formula > 0, Formula, otherwise blank)

    In each instance the formula needs to be fully closed off / completed. Once you add in the extra ) before the > 0, this will allow the IF statement to move through to the next element.

    Try:

    =IF(COUNTM(COLLECT({Issue}, {Review Date}, @cell <= DATE(2021, 1, 31), {Who Column}, CONTAINS("Smith", @cell), {Issue}, NOT(@cell = "N/A"))) >0, COUNTM(COLLECT({Issue}, {Review Date}, @cell <= DATE(2021, 1, 31), {Who Column}, CONTAINS("Smith", @cell), {Issue}, NOT(@cell = "N/A"))))

    ^Note that there are 4 closing parenthesis at the end as well... one for NOT, one for COLLECT, one for COUNTM, and a final one for IF.

    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Heather Hall

    It looks like you're just missing a closing parenthesis after your initial statement, before the > 0

    This is the structure of the IF statement:

    IF(Formula > 0, Formula, otherwise blank)

    In each instance the formula needs to be fully closed off / completed. Once you add in the extra ) before the > 0, this will allow the IF statement to move through to the next element.

    Try:

    =IF(COUNTM(COLLECT({Issue}, {Review Date}, @cell <= DATE(2021, 1, 31), {Who Column}, CONTAINS("Smith", @cell), {Issue}, NOT(@cell = "N/A"))) >0, COUNTM(COLLECT({Issue}, {Review Date}, @cell <= DATE(2021, 1, 31), {Who Column}, CONTAINS("Smith", @cell), {Issue}, NOT(@cell = "N/A"))))

    ^Note that there are 4 closing parenthesis at the end as well... one for NOT, one for COLLECT, one for COUNTM, and a final one for IF.

    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Heather Hall
    Options

    That worked perfectly! Thank you so much.

  • Genevieve P.
    Options

    No problem at all!

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!