INDEX COLLECT, how to collect multiple values in a range when the criteria matches
Answers
-
So we have confirmed that the dates are in fact dates and in date type columns. We know we are getting a match in both the COUNTIFS as well as the INDEX/COLLECT.
But when we use the COUNTIFS to generate the number for the INDEX/COLLECT, it breaks on random rows.
Let's try breaking it out into more helper columns (even though we shouldn't have to).
Insert another text/number column. Let's call this one "Index Number" and use this formula:
=COUNTIFS([Project Number]:[Project Number], @cell = [Project Number]@row, [Final Cost Code]:[Final Cost Code], @cell = [Final Cost Code]@row, Date:Date, @cell = Date@row, Row:Row, @cell<= Date@row)
This should output 1 on most rows but in those case where there is more than one person for that date/cost code/project it should output 1 on the first row, 2 on the next row, so on and so forth.
Then we can (try) amend our Employee Name column formula to:
=INDEX(COLLECT({Timesheet | Employee Name}, {Timesheet | Project}, [Project Number]@row, {Timesheet | Cost Code}, [Final Cost Code]@row, {Timesheet | Date}, Date@row), [Index Helper]@row)
-
Well that's interesting, because every row shows up zero. Big ol' goose egg. What does that mean?
-
That means my fingers weren't in tune with my brain. Here is the typo corrected. What does this do?
=COUNTIFS([Project Number]:[Project Number], @cell = [Project Number]@row, [Final Cost Code]:[Final Cost Code], @cell = [Final Cost Code]@row, Date:Date, @cell = Date@row, Row:Row, @cell<= Row@row)
-
It works. And so does the new Employee Name formula.
Bonus (For me anyways): I think I finally understand what Row Index means AND I may even understand what the Auto and Row Columns are doing now.
Now I just need to update all of my billing detail sheets that are project specific. No big deal. 😆
Thank you so much for all of your help! This has been so much fun for me.
-
Happy to help. 👍️
Glad we were able to (finally) get it sorted, and definitely glad to hear that you are understanding more!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!