Proper way to use both AND and OR in a CountIFS formulat

BrianJ

Paul Newcome

The proper syntax for what you were originally trying would have still backfired on you a bit even if you had gotten the correct syntax because technically "Cancelled" is not "Completed" which means you would have included "Cancelled" in your count. Same logic would have included "Completed" an "On Hold" in your count. When you want to EXCLUDE multiple options, you would just use the AND statement.

AND(@cell <> "This", @cell <> "That")

Generally speaking though, I would have suggested your second (and successful) formula instead simply because there are less variables to include than there are to exclude.

But… The way to incorporate the AND and is not blank with the OR would have been along the lines of

=COUNTIFS({Range}, AND(@cell <> "", OR(@cell = "This", @cell = "That")))

Paul Newcome

Are you able to provide more details as to exactly what you are trying to do?

BrianJ

Sorry, I accidently hit send without filling in the information. Then I figured it out, but could not figure out a way to delete the post. Thanks for getting back to me. If you know how to delete a post, let me know.

Paul Newcome

If you would like to post your solution instead of deleting your thread, it may be useful for others to see what you came up with. Otherwise, you can "Flag" your post and use the "Report" option to let the moderators know you want it deleted.

BrianJ

Thanks Paul. I have a project sheet that I was trying to use a formula to create a summary item for tasks that were overdue. Originally, I was using a COUNTIFS function for using the Column End Date and an OR function that was using the Status column and a series of OR (@cell<> "Condition1", @cell<>"Conditon2"), but it was pulling in the blanks, which is not what I wanted. So, I was trying to use the AND in conjunction with the OR statement to specify Status:Status, AND((NOT(ISBLANK(@cell),OR(@cell<>"Completed", @cell<>"Canceled", @cell<>"On Hold"), but kept getting error messages. The entire formula would have been something like:

=COUNTIFS([End Date]:[End Date], <Today(), Status:Status, AND((NOT(ISBLANK(@cell)),OR(@cell<>"Completed", @cell<>"Canceled", @cell<>"On Hold")).

I then thought about it again and decided to rewrite with just using an OR formula specifying the Status conditions that I wanted to count.

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

This ended up working. Not sure if you have any insights into the original formula that could be helpful to others.

Paul Newcome

The proper syntax for what you were originally trying would have still backfired on you a bit even if you had gotten the correct syntax because technically "Cancelled" is not "Completed" which means you would have included "Cancelled" in your count. Same logic would have included "Completed" an "On Hold" in your count. When you want to EXCLUDE multiple options, you would just use the AND statement.

AND(@cell <> "This", @cell <> "That")

Generally speaking though, I would have suggested your second (and successful) formula instead simply because there are less variables to include than there are to exclude.

But… The way to incorporate the AND and is not blank with the OR would have been along the lines of

=COUNTIFS({Range}, AND(@cell <> "", OR(@cell = "This", @cell = "That")))