SOLVED

Help with COUNTIF with multiple critera

Occasional Contributor

Hi

 

I have tried the below formula, however it is not working. I would like to count the rows if the date (SDMF Tasklist (build)'!E:E) is due within the next 7 days but it also has to have the status "not started"

 

=COUNTIFS('SDMF Tasklist (build)'!E:E,">"&TODAY()+7,'SDMF Tasklist (build)'!C:C,"Not Started")

 

thanks in advance

3 Replies
also, how would i create my next formula, which would need to be:

count the rows if the date (SDMF Tasklist (build)'!E:E) is due in 7-14 days but it also has to have the status "not started".
best response confirmed by Row89 (Occasional Contributor)
Solution

@Row89 

=COUNTIFS('SDMF Tasklist (build)'!E:E,"<="&TODAY()+7,'SDMF Tasklist (build)'!E:E,">="&TODAY(),'SDMF Tasklist (build)'!C:C,"Not Started")

You can try this formula if the due date is from TODAY up to TODAY+7.

=COUNTIFS('SDMF Tasklist (build)'!E:E,">="&TODAY()+7,'SDMF Tasklist (build)'!E:E,"<="&TODAY()+14,'SDMF Tasklist (build)'!C:C,"Not Started")

You can try this formula if the due date is from TODAY +7 up to TODAY+14.

Thanks so much, this worked!

I'm trying to do another formula, and it's not showing what I want it to:

=IF(ISBLANK([@[Date Completed]]),"",IF(AND([@[Date Completed]]<=[@[Due Date]]), "Completed on Time", "Completed Late"))

if the date completed is blank AND the status column is "completed" then show "Missing completed date" but if the completed date column contains a date and is equal to or less than the "due date" AND the status column is "completed" then show "Completed on time" otherwise if the completed date is more than the due date and the status column is "completed" then show "Completed late"

I'm having trouble only picking up "completed" items in the status column as it contains other statuses in there too like "not started".