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
- Customer Resources
- 66.2K Get Help
- 431 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 74 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!