Countifs with date range

Mike Norman

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?

Kelly Moore

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

Mike Norman

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