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.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!