count if the service date is with in a date range.
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.
Best Answer
-
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))), "")
Answers
-
Here is another formula i was working on. It didn't work either.
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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...
-
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))
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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))), "")
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives