COUNT COLLECT To Find Rows For a Specific Date Not Working

SoS | Dan Palenchar
SoS | Dan Palenchar ✭✭✭✭✭✭
edited 04/09/20 in Formulas and Functions


I have a JOB SHEET that shows jobs assigned to one or more persons in a "Resource" contact column. This sheet also has the dates for "Start" and "Finish" to determine the date range that the project will be worked on.

I have a separate Sheet (Sheet 2) that is intended to return the number of projects a specific person has assigned to them on each day of the week. I have formulas that return the first monday of the week in a Week Start column for the current and next three weeks. There are then 5 columns for Monday through Friday. Each of these 5 columns should return the number of jobs assigned to a particular person (let's call him Joe) that falls on that day of the week for the week in question. So the Monday column would count all jobs where the Week Start date is within the Date Range on the JOB SHEET. The Tuesday column would work the same way, but it would be Week Start date + 1.

The formula I have is somewhat working, for Monday:

=COUNT(COLLECT({Start}, {Start}, $[Week start]@row >= @cell, {Finish}, $[Week start]@row <= @cell, {Resource}, HAS("Joe", @cell)))

For Tuesday:

=COUNT(COLLECT({Start}, {Start}, $[Week start]@row +1 >= @cell, {Finish}, $[Week start]@row + 1 <= @cell, {Resource}, HAS("Joe", @cell)))

Wednesday/Thursday/Friday are the same, just with +2/+3/+4 instead of +1.

To check for accuracy, I've filtered the JOB SHEET to see Joe's Jobs during the range of 4/6-4/10. There is 1 job where Monday (4/6) is in range.

However, my formula is showing 2 jobs on Monday.

Then, for the range of 4/13-4/17, Joe has two jobs

However, the formulas return a false positive for Friday

I'm not sure why these false values are coming in, if anyone could help I'd appreciate it. Thanks!

👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!