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?

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.