#Nested Criteria when combining formulas together

I am trying to get this formula to work, but it kicks back the nested criteria.

=JOIN(DISTINCT(COLLECT([Employee Number]:[Employee Number], [Created Date]:[Created Date], IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()), [Employee Number]:[Employee Number], COUNTIF([Employee Number]:[Employee Number], @cell) >= 2)), ", ")

I just want to see the employee number that populates 2 or more time during the current month, is this possible?

Find more posts tagged with

Formulas

Join

Countif

Accepted answers

All comments

KPH

Could you split this into two steps?

Put the COUNTIF part into a new column in your sheet and then in your COLLECT reference that column and check it is >=2.

Step 1

new column formula (I called this column "Instances this month")

=COUNTIFS([Employee Number]:[Employee Number], [Employee Number]@row, [Created Date]:[Created Date], IFERROR(MONTH(@cell ), 0) = MONTH(TODAY()))

Step 2

Simplify the COLLECT to use the new column instead

=JOIN(DISTINCT(COLLECT([Employee Number]:[Employee Number], [Instances this month]:[Instances this month], >=2)), ", ")

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.