CountIF with Criteria including date range

Evan Summerville

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

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))

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))