# COUNTIFS With OR

#### meppwc

##### Active Member
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"

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Weazel

##### Well-known Member
maybe something like...

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

#### meppwc

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

#### Weazel

##### Well-known Member
ok, for some reason this is hurting my head

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

#### Jonmo1

##### MrExcel MVP

=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

##### Well-known Member
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

##### Well-known Member
ahhhh, ok, I knew I was overthinking it, thanks for clearing the cobwebs

Replies
3
Views
84
Replies
4
Views
92
Replies
2
Views
24
Replies
16
Views
148
Replies
4
Views
68