COUNT COLLECT To Find Rows For a Specific Date Not Working
Hello,
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!
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Answers
-
Try a COUNTIFS instead
=COUNTIFS({Start}, @cell<= $[Week Start]@row, {Finish}, @cell>= $[Week Start]@row, {Resource}, HAS(@cell, "Joe"))
You are also going to want to make sure to switch the range/criteria within the HAS function. You have criteria first, but it should be range (@cell) first.
-
Paul,
Thanks for the response. Unfortunately, I'm still getting the same data. Good catch with the HAS though, I switched over from CONTAINS and didn't reorder the arguments.
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
CONTAINS does not pick up on data in Contact type columns.
Try the COUNTIFS for Monday with the HAS(@cell, "Joe")
If it is still producing unexpected results, check your data. Are there any dates that are inadvertently overlapping the Monday? Are there any other contacts that may have "Joe" in them (this would include last names where "joe" is right in the middle for example).
-
So I do now have the COUNTIFS with HAS (@cell, "Joe") and the same results are coming through. I will comb the data a bit more to see if there are some values being picked up that I am not accounting for. Thanks for the help
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
-
Sure thing. Feel free to revisit with the outcome.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!