# How to add multiple status criteria when counting tasks

✭✭✭

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)))

Tags:

## Best Answer

• ✭✭✭✭✭✭
Answer ✓

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, MSLS, M.Ed.

Adult Education Specialist | Process Improvement Enthusiast

## Answers

• ✭✭✭✭✭✭
Answer ✓

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, MSLS, M.Ed.

Adult Education Specialist | Process Improvement Enthusiast

• ✭✭✭

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

• ✭✭✭✭✭✭

@Devin C. , my pleasure!

Amber Eakin, MSLS, M.Ed.

Adult Education Specialist | Process Improvement Enthusiast

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!