CountIF Formula issue

Evan Summerville

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!

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.

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.