INDEX COLLECT, how to collect multiple values in a range when the criteria matches

Paul Newcome

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)

Molly H

Well that's interesting, because every row shows up zero. Big ol' goose egg. What does that mean?

Paul Newcome

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)

Molly H

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.

Paul Newcome

Happy to help. 👍️

Glad we were able to (finally) get it sorted, and definitely glad to hear that you are understanding more!