Countifs and OR statement

Daryl Lee

I'm trying to have a formula that will work if one column matches specific criteria and then if another column matches one of two criteria. I have been able to get it to work for one of the criteria from the second column by writing

=(COUNTIFS({Assigned Owner}, [Assigned To]2, {Project Progress}, <>"Closed"))

However, whenever I try and make the second column be either <> "Closed" or <> "Closed - no action" I keep getting an error. I have tried both

=(COUNTIFS({Assigned Owner}, [Assigned To]2, {Project Progress}, OR(<>"Closed", <>"Closed - no action")))

and

=(COUNTIFS({Assigned Owner}, [Assigned To]2, {Project Progress}, <>"Closed", OR({Project Progress}, <>"Closed - no action")))

I'm not sure how to format this and I've looked through other questions but what I'm finding in those don't seem to work here.

Paul Newcome

Try this exact formula...

=COUNTIFS({Assigned Owner}, [Assigned To]2, {Project Progress}, OR(@cell <>"Closed", @cell <>"Closed - no action"))

Here is another option...

=COUNTIFS({Assigned Owner}, [Assigned To]2, {Project Progress}, NOT(CONTAINS("Closed", @cell)))

Paul Newcome

When using an OR statement, it is looking for logical statements. You only need to add @cell references.

=(COUNTIFS({Assigned Owner}, [Assigned To]2, {Project Progress}, OR(@cell <>"Closed", @cell <>"Closed - no action")))

However... Your current formula would not work as you intend it to. "Closed" is not "Closed - no action" and the other way around. Try replacing the OR with the AND function.

There are a few other solutions that could be used, so if you can't get this one working then just feel free to let me know.

Daryl Lee

Thank you so much, Paul! I have tried to simulate what you put and have created

=(COUNTIFS({Assigned Owner}, [Assigned To]2, {Project Progress}, OR({Project Progress}, <>"Closed", {Project Progress}, <>"Closed - no action")))

I'm not wanting to reference a specific cell, but rather a range. Will that not work?

And wouldn't work as I want to be able to work if it's either one of them, but it needs to be one of them. I tried doing and statement and it was adding if either of them wasn't, which then just provided the full amount as any of them would fit being not one of them if that makes sense.

Paul Newcome

Try this exact formula...

=COUNTIFS({Assigned Owner}, [Assigned To]2, {Project Progress}, OR(@cell <>"Closed", @cell <>"Closed - no action"))

Here is another option...

=COUNTIFS({Assigned Owner}, [Assigned To]2, {Project Progress}, NOT(CONTAINS("Closed", @cell)))

Daryl Lee

Thank you so much, Paul. I apologize for not understanding your original message for the formula.

Both worked in the sense that I did not get any errors. However, the first formula was doing a combination of if the column didn't contain either, whereas I needed a count of excluding the total amount of them.

the second equation worked perfectly and gave me the information that I needed.

Thank you so much for your help!

Paul Newcome

Happy to help! 👍️