Null value for COUNTM COLLECT

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

Find more posts tagged with

Accepted answers

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

All comments

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:

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.