✭✭✭✭

Hi,

I am trying to calculate the number of upcoming tasks in the next 15 days.

This formula works BUT the number returned is not correct. Any idea why?

=COUNTIFS(Milestone:Milestone, CONTAINS("V2", @cell), [Published due date]:[Published due date], =TODAY(+15))

"V2" is in a text column with no other text in it

I have tried using HAS instead but I get the same number returned.

Below an example, there should be at least 6 tasks returned but I get 5 with the formula above

• ✭✭✭✭✭✭

In your above screenshot I only see 6 rows that match your criteria. The row containing "Pricing+" is "SS".

• ✭✭✭✭

Thanks Paul, good point. Assuming that's correct (which may be!) when I apply the same formula but to calculate upcoming tasks in next 30 days, I get 0 returned.. which doesn't seem right..

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], =TODAY(+30))

• ✭✭✭✭✭✭

Are you trying to get all tasks within the next 30 days or tasks that are 30 days out? Your formula currently says EQUALS 30 days in the future, but it seems as if you want everything LESS THAN OR EQUAL TO 30 days in the future.

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell <= TODAY(+30))

• ✭✭✭✭

I'm trying to get all tasks within the next 30 days

• ✭✭✭✭✭✭

Then you are going to want to try the above. If that starts pulling in past dates since yesterday is technically less than 30 days in the future we can establish both ends of the range by stating less than or equal to +30 days AND greater than or equal to today (meaning not in the past).

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], AND(@cell >= TODAY(), @cell <= TODAY(+30)))

• ✭✭✭✭

Thanks! It seems to work by using:

Next 15 days: =COUNTIFS(Milestone:Milestone, CONTAINS("V2", @cell), [Published due date]:[Published due date], =TODAY(+15))

Next 30 days: =COUNTIFS(Milestone:Milestone, CONTAINS("V2", @cell), [Published due date]:[Published due date], @cell <= TODAY(+30))

• ✭✭✭✭✭✭

The Next 15 Days formula will only count those that are exactly 15 days away (= vs <=). So those rows that it is counting today will not be counted tomorrow.

• ✭✭✭✭

Thanks Paul, I can see that now the Next 15 days formula is down to 0.

However if I use the Next 30 days formula I get the same result. So

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell <= TODAY(+15))

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell <= TODAY(+30))

Gives me the same result which is incorrect. Any suggestion?

• ✭✭✭✭✭✭

Are you sure that you have rows that are more than 15 days in the future but less than 30?

• ✭✭✭✭

I should have 1 item within the next 15 days and 3 in next 30 days, following the report that looks at all SS tasks

I don't get why it's giving me 9 as a results...

• ✭✭✭✭✭✭

Try adding in the greater than today argument as I had above to exclude items in the past.

• ✭✭✭✭

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell <= TODAY(+15))

Gives me 9 as a result

=COUNTIFS(Milestone:Milestone, CONTAINS("SS", @cell), [Published due date]:[Published due date], @cell = TODAY(+15))

Gives me 0 as a result

Both incorrect 😕

• ✭✭✭✭

Works perfectly! Thanks for your patience on this! 🤗

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!