COUNTIFS with OR - Syntax Questions

Matt Galindo

I have a sheet that has a TARGET DATE and STATUS. I want a count of rows where the STATUS is either WIP or NEW and the DATE < Today. The goal is a count of rows that are overdue. I've been trying different variations of this formula.

=COUNTIFS({TARGET DATE}:{TARGET DATE}, < TODAY(), {STATUS}, or(@CELL = "New", @CELL = "WIP"))

I'm pretty sure it's a syntax issue, but I have not hit on the right combination.

Andrée Starå

@Matt Galindo

Try.

=COUNTIFS([TARGET DATE]:[TARGET DATE], iferror(@cell <TODAY(),0), STATUS:STATUS, or(@cell = "New", @cell = "WIP"))

Did that work?

Samuel Mueller

@Matt Galindo

=COUNTIFS({TARGET DATE}:{TARGET DATE}, iferror(@cell <TODAY(),0), {STATUS}, or(@cell = "New", @cell = "WIP"))

Try this and make sure to lowercase @cell

Andrée Starå

Hi @Matt Galindo

I hope you're well and safe!

To add to Samuel's excellent advice/answer.

Remove the second target date, :{TARGET DATE}

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Please support the Community by marking the post(s) that helped or answered your question or solved your problem with the accepted answer/helpful, Insightful/Vote Up/Awesome. It will make it easier for others to find a solution or help to answer!

Samuel Mueller

Ahhhh thank you @Andrée Starå !! Missed that one.

Andrée Starå

@Samuel Mueller

Happy to help! Easy to miss!

Matt Galindo

Thanks for the help. I'm still having an issue - #INVALID REF. Based on the error message text, I tried replacing the { with [, but that did not work - got an #UNPARSABLE message.

Here is what I have now:

=COUNTIFS({TARGET DATE}, IFERROR(@cell < TODAY(), 0), {STATUS}, OR(@cell = "New", @cell = "WIP"))

Any suggestions would be appreciated.

Samuel Mueller

Invalid Ref usually means your reference name in the curly brackets is wrong. Check the name of your cross sheet references to make sure they match "Target Date" and "Status"

Matt Galindo

Sorry, should have mentioned - this is all in one sheet - that's why I went to the brackets.

Samuel Mueller

Then your ranges need to be [target date]:[target date], and status:status

No brackets needed on status

Andrée Starå

@Matt Galindo

Try.

=COUNTIFS([TARGET DATE]:[TARGET DATE], iferror(@cell <TODAY(),0), STATUS:STATUS, or(@cell = "New", @cell = "WIP"))

Did that work?

Matt Galindo

Andre & Samuel,

Thank you for your help. That last iteration worked!

Have a great rest of your day.

Andrée Starå

@Matt Galindo

Excellent!

You're more than welcome!