Trying to count if data range falls between two dates.

I have a sheet with StartDate and EndDate fields. I want to count actual items opened on a separate sheet if the date falls between the start date and end date. The below formula keeps returning #UNPARSEABLE and I suspect its something minor.

=COUNTIFS({Request Tracker with Form Range 1}, "<=" & (EndDate@row),{Request Tracker with Form Range 1}, ">=" & (StartDate@row))

Find more posts tagged with

Formulas

Countif

Accepted answers

heyjay

=COUNTIFS(
{Request Tracker with Form Range 1}, <= (EndDate@row),
{Request Tracker with Form Range 1}, >= (StartDate@row))

and make sure that the date columns and actually formatted as DATE in their properties.

Alex_L_Oncor

=COUNTIFS({Request Tracker Form StartDate},>=[StartDate]@row,{Request Tracker Form EndDate},<=[EndDate]@row)

I think that should work or something similar. If not a way to surely accomplish this would be to add a column to your other sheet. Make that column have a text like "Between"

=IF(AND(DATE@row>=[StartDate]@row, DATE@row <= [EndDate]@row),"Between","")

On the separate Sheet your equation can now be

=COUNTIF({Between?},"Between")

Hope this helps.

All comments

heyjay

=COUNTIFS(
{Request Tracker with Form Range 1}, <= (EndDate@row),
{Request Tracker with Form Range 1}, >= (StartDate@row))

and make sure that the date columns and actually formatted as DATE in their properties.

Alex_L_Oncor

=COUNTIFS({Request Tracker Form StartDate},>=[StartDate]@row,{Request Tracker Form EndDate},<=[EndDate]@row)

I think that should work or something similar. If not a way to surely accomplish this would be to add a column to your other sheet. Make that column have a text like "Between"

=IF(AND(DATE@row>=[StartDate]@row, DATE@row <= [EndDate]@row),"Between","")

On the separate Sheet your equation can now be

=COUNTIF({Between?},"Between")

Hope this helps.

Art A

That worked, thanks! There is enough of a difference in syntax from what I'm use to I get stuck on some trivial stuff sometimes.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.