Countifs with date range

Options

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Mike Norman ✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!