Formula to COUNT Number of Tasks Due in 30, 60, and 90 Days

Aimee Eckert

Hello,

I am trying to figure out the COUNTIF function to count # of tasks due in 30, 60, and 90 days in the sheet summary. I have already made columns for 30, 60, and 90 days.

Sing C

Hi @Aimee Eckert,

If I understand the ask correctly you are looking to capture in the sheet summary, the number of tasks where the Target End Date column (from your screenshot) is in the next 30, 60 or 90 days?

So, taking the following as examples:

you would expect to see # of tasks in next 30 days = 1 (only Mar 1st task), # of tasks in next 60 days = 2 (this would include the Mar 1st task and the Apr 1st task), and # of tasks in next 90 days = 2 (since there are no additional tasks that have a timeframe of that extra 30 days between 60-90).

In this case, you can do it without needing the additional columns, you just need to use the TODAY function for the comparison in your sheet summary fields.

The assumption I'm making is that you don't want to include tasks where the target date is in the past, i.e. the two tasks dated Feb 11 and Feb 15 in my example. If this is the case, then use the following formula in your '30 day' sheet summary field:

=COUNTIFS([Target End Date]:[Target End Date], >=TODAY(), [Target End Date]:[Target End Date], <=TODAY(30))

If you do want to include the dates with the historic target dates, you can use the COUNTIF formula and just remove the first condition check, so you end up with:

=COUNTIF([Target End Date]:[Target End Date], <=TODAY(30))

This would show 3 (the Feb 11, Feb 15, and Mar 1 tasks).

Then all you need to do for 60 and 90 is copy the formula into your other sheet summary fields and change the number in the TODAY function to 60 and 90 respectively.

I hope that helps!

Regards,

@Sing C

Sing C

Hi @Aimee Eckert,

If I understand the ask correctly you are looking to capture in the sheet summary, the number of tasks where the Target End Date column (from your screenshot) is in the next 30, 60 or 90 days?

So, taking the following as examples:

you would expect to see # of tasks in next 30 days = 1 (only Mar 1st task), # of tasks in next 60 days = 2 (this would include the Mar 1st task and the Apr 1st task), and # of tasks in next 90 days = 2 (since there are no additional tasks that have a timeframe of that extra 30 days between 60-90).

In this case, you can do it without needing the additional columns, you just need to use the TODAY function for the comparison in your sheet summary fields.

The assumption I'm making is that you don't want to include tasks where the target date is in the past, i.e. the two tasks dated Feb 11 and Feb 15 in my example. If this is the case, then use the following formula in your '30 day' sheet summary field:

=COUNTIFS([Target End Date]:[Target End Date], >=TODAY(), [Target End Date]:[Target End Date], <=TODAY(30))

If you do want to include the dates with the historic target dates, you can use the COUNTIF formula and just remove the first condition check, so you end up with:

=COUNTIF([Target End Date]:[Target End Date], <=TODAY(30))

This would show 3 (the Feb 11, Feb 15, and Mar 1 tasks).

Then all you need to do for 60 and 90 is copy the formula into your other sheet summary fields and change the number in the TODAY function to 60 and 90 respectively.

I hope that helps!

Regards,

@Sing C

Aimee Eckert

Thank you very much! I really appreciate your thorough response. As always, great service!

Sing C

Of course. You're most welcome! @Aimee Eckert