CountIF Formula issue

Greetings,

I'm currently attempting to create a CountIF function that counts the number of Ungroomed Stories that start within the next 30 days. My formula is the following:

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

This formula appears to work, but something is amiss. When I run a filter for "Stories" that are "To Do", don't have any label, and start in the next 30 days I get 1 result (see screen shot). However, when I run my formula, the countif function tells me there are 4 stories that are not groomed.

Is there an issue with my formula syntax somewhere?

Filter:

Output: one story meeting criteria

Formula Output: four stories meeting criteria

Any help is appreciated!

Find more posts tagged with

Formulas

functionality

Sheet Summary

Accepted answers

Jon Baier

I am guessing it is something different between <=TODAY(30) and what the filter is evaluating as 'is in the next (days) = 30.

Maybe use a formula that tests if the date is between today and today +30?

Jon Baier

Good deal!

Just a thought, if you wanted to include any rows with a start date that includes today, you would want it to be

......[Target Start Date], >=TODAY(), [Target Start Date]:[Target Start Date], <=TODAY(30)

ie change the 'greater than today' to 'greater than or equal to' today.

All comments

Jon Baier

I am guessing it is something different between <=TODAY(30) and what the filter is evaluating as 'is in the next (days) = 30.

Maybe use a formula that tests if the date is between today and today +30?

Evan Summerville

Hey Jon,

That idea worked, thanks for the insight!

Updated Formula:

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

Cheers,

Evan

Jon Baier

Good deal!

Just a thought, if you wanted to include any rows with a start date that includes today, you would want it to be

......[Target Start Date], >=TODAY(), [Target Start Date]:[Target Start Date], <=TODAY(30)

ie change the 'greater than today' to 'greater than or equal to' today.

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.