Countifs with date range
Hello everyone, I am attempting to create a formula that references another sheet with multiple IF components, and I am stumbling with the date range.
I currently have:
=COUNTIFS({OPUS Range 1}, "Western New England", {OPUS Range 4}, "Completed", AND(@cell >= {OPUS Range 3}DATE(2021, 01, 01), @cell <= DATE(2021, 01, 31)))
I want to pull all completed projects within the month of January - does anyone have advice on what I need to change?
Answers
-
Hey @Mike Norman
The COUNTIFS formula has the syntax of COUNTIFS(range1, criteria1, range2, criteria2, etc). The date portion does not follow the range , criteria format
=COUNTIFS({OPUS Range 1}, "Western New England", {OPUS Range 4}, "Completed", {OPUS Range 3}, AND(ISDATE(@cell), @cell >= DATE(2021, 01, 01), @cell <= DATE(2021, 01, 31)))
Another approach is to use the MONTH function. However, since date functions may generate errors, it is often necessary to insure that non date cells will not be counted (I added the ISDATE() function to the formula above as well)
=COUNTIFS({OPUS Range 1}, "Western New England", {OPUS Range 4}, "Completed", {OPUS Range 3}, ISDATE(@cell), {OPUS Range 3}, MONTH(@cell)=1)
To incorporate good practices in future formulas, consider renaming the cross sheet reference generic range names with the actual names of your column formulas. This will help you and the smartsheet community better understand the formulas.
cheers
-
Thank you for replying Kelly! Unfortunately, I am now getting an "Incorrect Argument" error.
You are right, I should've included the column names from the source sheet, including now.
OPUS Range 1 = Team/Region
OPUS Range 4 = Stage
OPUS Range 3 = End Date
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!