COUNT COLLECT To Find Rows For a Specific Date Not Working

SoS | Dan Palenchar

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!

Paul Newcome

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.

SoS | Dan Palenchar

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.

Paul Newcome

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).

SoS | Dan Palenchar

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

Paul Newcome

Sure thing. Feel free to revisit with the outcome.