#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
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!