What is wrong with my countifs data is between 2 dates formula?

Hello team,

I'm trying to calculate my data if the status' end date is between 2 dates.

The formula I'm using is:

=COUNTIFS({Project Plan "Status"}, Item@row, {Project Plan "End Date"}, >[Date1]10, {Project Plan "End Date"}, <=[Date2]10, {Project Plan "Play Group"}, Item10)

In my external smartsheet project plan, I have the following columns:

Status = Disc, Des, P/S, Done

End Date = There is an end date for each status listed above

Play Group = the grouping in which each data point should be deployed

In this sheet, I'm looking to populate the 1/31/2023 column using the formula listed above. My column names for the dates are: Date1, Date2, Date3, Date4...etc...

The results I am receiving in all of the cells is 0.

My columns are set as date columns however I changed to "text/number" columns and still no values just 0.

any ideas?

Find more posts tagged with

Accepted answers

Samuel Mueller

@toni.toni601 no it would have to be in a date column in order for it to recognize it as a date. both the range column "{Project Plan "End Date"}" would have to be a date column and the [Date1], [Date2] etc. would have to be date columns.

All comments

Samuel Mueller

@toni.toni601 try adding @cell

=COUNTIFS({Project Plan "Status"}, Item@row, {Project Plan "End Date"}, @cell >[Date1]10, {Project Plan "End Date"}, @cell <=[Date2]10, {Project Plan "Play Group"}, Item10)

toni.toni601

:-( Still getting results 0 (zero) when there should be data...

If my column is set as a Text/Number column, does Smartsheet still recognize the data in the cell as a date?

I tried using just the countifs between the 2 dates and still resulted in "0" zero.

Samuel Mueller

toni.toni601

Changing the column to a date column worked perfectly when I added the @cell. THANK YOU

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.