count if the service date is with in a date range.

Dakota

I need to count how many times per week the team shows up in a date range. I have many different teams working 52 weeks and i need to make the formula the same for each row.

Dakota

that formula would not work... i had to use a differnt formula for each week of the year. ie :

=IFERROR(COUNTIFS({[9. September] 2022 Master CC Schedule Range 1}, Route@row, {[9. September] 2022 Master CC Schedule Range 2}, AND(@cell >= DATE(2022, 9, 26), @cell <= DATE(2022, 10, 2))), "")

Dakota

Here is another formula i was working on. It didn't work either.

Jeff Reisman

You don't need the AND in the first formula.

I don't understand why you are using IFERROR with your COUNTIFS, unless you're trying to suppress formula error messages caused by blank cells, etc,. in which case you should be using =IF(ISERROR(COUNTIFS([....). If that is the case, you need to list your formula twice in the IF(ISERROR( formula; the first time to check for an error, and the second time to show your formula results if there isn't an error.

You may also be getting screwed up by having "[1.]" in the name of your ranges.

Dakota

I don't know why I have the IFERROR. There are a lot of blank cells that will later have information in them. I'm just trying to get a formula to look for the team name, then to look for a date within the [week start:] and [week end:]. I may need to add 5 more columns for each day of the week...

I tried the multiple date columns with no luck...

Jeff Reisman

I would recommend updating your range names to remove the "[1.]" and possibly remove the colons from your Week Start and Week End column names.

To deal with blank values, you're on the right track. The logic is:

IF there ISERROR in this COUNTIFS formula, then set the cell value to blank, otherwise use the COUNTIFS formula.

So...

=IF(ISERROR(COUNTIFS({Hidden Master Schedule Range 3}, >=[Week Start]@row, {Hidden Master Schedule Range 2}, = Team@row, {Hidden Master Schedule Range 1}, <=[Week End]@row)), "", COUNTIFS({Hidden Master Schedule Range 3}, >=[Week Start]@row, {Hidden Master Schedule Range 2}, = Team@row, {Hidden Master Schedule Range 1}, <=[Week End]@row))

Dakota

that formula would not work... i had to use a differnt formula for each week of the year. ie :

=IFERROR(COUNTIFS({[9. September] 2022 Master CC Schedule Range 1}, Route@row, {[9. September] 2022 Master CC Schedule Range 2}, AND(@cell >= DATE(2022, 9, 26), @cell <= DATE(2022, 10, 2))), "")