CountIF with Criteria including date range

Hello,

I'm currently trying to create a countifs formula to count the number of "issue type"(column) "story" where it meets certain criteria including falling within the next 30 days.

My base formula works here:

=COUNTIFS([Issue Type]:[Issue Type], "Story", Labels:Labels, "Groomed", Status:Status, "To Do")

However, when I try to add criteria for these "stories" that have a start date within 30 days I get the INVALID OPERATION error. My current formula for it (that is not working) is here:

=COUNTIFS([Issue Type]:[Issue Type], "Story", Labels:Labels, "Groomed", Status:Status, "To Do", [Target Start Date]:[Target Start Date] < TODAY(+30))

Any Idea what I'm doing wrong here? Help appreciated!

Thanks

Find more posts tagged with

Sheets

Formulas

functionality

IT & Operations

Accepted answers

Paul Newcome

Try using an @cell reference in your CONTAINS function.

=COUNTIFS([Issue Type]:[Issue Type], "Story", Labels:Labels, CONTAINS("Groomed", @cell), Status:Status, "To Do", [Target Start Date]:[Target Start Date], <TODAY(30))

All comments

Evan Summerville

I should note that some of the "Target Start Date" Fields are blank, would this cause an issue?

Evan Summerville

I've gotten the following formula to run, however it returns "0" based on the criteria, which isn't right.

=COUNTIFS([Issue Type]:[Issue Type], "Story", Labels:Labels, "Groomed", Status:Status, "To Do", [Target Start Date]:[Target Start Date], <TODAY(30))

Evan Summerville

Update formula as Labels is a string that can have more text than a single entry:

=COUNTIFS([Issue Type]:[Issue Type], "Story", Labels:Labels, CONTAINS("Groomed", Labels:Labels), Status:Status, "To Do", [Target Start Date]:[Target Start Date], <TODAY(30))

Still no results, am I using the Contains function correctly?

Paul Newcome

Try using an @cell reference in your CONTAINS function.

=COUNTIFS([Issue Type]:[Issue Type], "Story", Labels:Labels, CONTAINS("Groomed", @cell), Status:Status, "To Do", [Target Start Date]:[Target Start Date], <TODAY(30))

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.