Formula: looking for BOTH "In Progress" & "Not Started" Tasks

System

This discussion was created from comments split from: Count If Current Week.

Sunny Kaul

Hi Paul,

I'm looking for results on the below condition :

•New Tasks that are upcoming only for next week (In Progress & Not Started)

When I use the below formula, I get "In Progress" Tasks as expected.

=COUNTIFS([End Date]:[End Date], <=TODAY(7), [End Date]:[End Date], >TODAY(),[Status:Status],"In Progress")

***But I'm looking for BOTH "In Progress" & "Not Started" Tasks. Using the below formula I get an error #PARSEABLE...

=COUNTIFS([End Date]:[End Date], <=TODAY(7), [End Date]:[End Date], >TODAY(),[Status:Status],"In Progress",AND[Status:Status],"Not Started")

Please help me correct the formula.

Thank you

Paul Newcome

@Sunny Kaul Try this...

=COUNTIFS([End Date]:[End Date], @cell <=TODAY(7), [End Date]:[End Date], @cell >TODAY(), [Status:Status], OR(@cell = "In Progress", @cell = "Not Started"))

Sunny Kaul

Sorry Paul, but =Could you specify what @cell represents?

COUNTIFS([End Date]:[End Date], @cell <=TODAY(7), [End Date]:[End Date], @cell >TODAY(), [Status:Status], OR(@cell = "In Progress", @cell = "Not Started"))

Paul Newcome

"@cell" is essentially telling the formula to evaluate the previously established range on a cell by cell basis.

=COUNTIFS([End Date]:[End Date], @cell <=TODAY(7), [End Date]:[End Date], @cell >TODAY(), Status:Status, OR(@cell = "In Progress", @cell = "Not Started"))

The bold portion is basically saying to count the rows in the Status column where the cell equals "In Progress" or the cell equals "Not Started".

Sunny Kaul

Thank you, Paul! It's giving me the expected results! :)

Paul Newcome

Happy to help. 👍️