How to add multiple status criteria when counting tasks

Devin C.

I am looking to create two summary sheet fields.

Formula 1. Count the number of tasks that are due within the next 14 days

• Constraints:
  • Status must be "Not Started, In Progress, OR Scheduled
  • Due Date must be within the next 14 days

Formula 2. Count the number of tasks that are due within the next 14 days but also return values of tasks where due date is older then TODAY.

• Constraints:
  • Status must be "Not Started, In Progress, OR Scheduled
  • Due Date must be within the next 14 days but also older then TODAY.

This formula gets me part of #1. I am having issues adding multiple statuses and getting the correct returns. Any help would be greatly appreciated.

=COUNTIFS(Status:Status, "In Progress", [Due Date]:[Due Date], AND(@cell >= TODAY(), @cell <= TODAY(+14)))

Amber Eakin

Hi @Devin C. - Much like the AND function, you can use OR to add in the multiple statuses.

Formula 1: =COUNTIFS(Status:Status, OR(@cell="In Progress", @cell="Not Started", @cell="Scheduled"), [Due Date]:[Due Date], AND(@cell >= TODAY(), @cell <= TODAY(+14)))

Formula 2 (removing the constraint of only being in the next 14 days and not also in the past: =COUNTIFS(Status:Status, OR(@cell="In Progress", @cell="Not Started", @cell="Scheduled"), [Due Date]:[Due Date], <= TODAY(+14))

Please let me know if those work for you!

Amber Eakin

Hi @Devin C. - Much like the AND function, you can use OR to add in the multiple statuses.

Formula 1: =COUNTIFS(Status:Status, OR(@cell="In Progress", @cell="Not Started", @cell="Scheduled"), [Due Date]:[Due Date], AND(@cell >= TODAY(), @cell <= TODAY(+14)))

Formula 2 (removing the constraint of only being in the next 14 days and not also in the past: =COUNTIFS(Status:Status, OR(@cell="In Progress", @cell="Not Started", @cell="Scheduled"), [Due Date]:[Due Date], <= TODAY(+14))

Please let me know if those work for you!

Devin C.

Amber E, You are a rock star. Thank you very much for the help and quick response.

Amber Eakin

@Devin C. , my pleasure!