COUNTIFS With OR

[meppwc]

Can someone help me with this syntax using COUNTIFS with OR

I want to count the number of cells where:
- Row D equals "Severity 1"
- Row G equals "Production" or "PVT"

 

[Weazel]

maybe something like...

=SUM(COUNTIFS($D$2:$D$10,"Severity 1",$G$2:$G$10,{"Production","PVT"}))

 

[meppwc]

Thanks Weazel.............that works perfectly............what about if:
- Row D equals "Severity 1"
- Row G is not equal to "Production" or "PVT"

 

[Weazel]

ok, for some reason this is hurting my head

=SUM(COUNTIFS($D$2:$D$10,"Severity 1",$G$2:$G$10,"<>"&{"production","pvt"}))

 

[Jonmo1]

=SUM(COUNTIFS($D$2:$D$10,"Severity 1",$G$2:$G$10,"<>"&{"production","pvt"}))

That's going to double count because the SUM(COUNTIFS()) syntax basically creates 2 countifs formulas, 1 for <>production, and 1 for <>pvt. Then sums the result of the 2.

You need to just add a 3rd criteria to a plain old countifs
COUNTIFS($D$2:$D$10,"Severity 1",$G$2:$G$10,"<>Production",$G$2:$G$10,"<>PVT")

 

[XOR LX]

ok, for some reason this is hurting my head

=SUM(COUNTIFS($D$2:$D$10,"Severity 1",$G$2:$G$10,"<>"&{"production","pvt"}))

I don't think that logic is correct. You can't use an "OR" condition with "not equal to", if you think about it.

Use an "AND" condition:

=COUNTIFS($D$2:$D$10,"Severity 1",$G$2:$G$10,"<>Production",$G$2:$G$10,"<>PVT")

Regards

 

[Weazel]

ahhhh, ok, I knew I was overthinking it, thanks for clearing the cobwebs