#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?
Answers
-
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)), ", ")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!