Null value for COUNTM COLLECT

Heather Hall

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

Genevieve P.

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

Genevieve P.

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

Heather Hall

That worked perfectly! Thank you so much.

Genevieve P.

No problem at all!