COUNTIFS With OR

meppwc

Active Member
Joined
May 16, 2003
Messages
423
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"
 

Some videos you may like

Excel Facts

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

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
maybe something like...

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

meppwc

Active Member
Joined
May 16, 2003
Messages
423
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
Joined
Dec 24, 2011
Messages
3,155
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
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

=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
Joined
Jul 2, 2012
Messages
4,517
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
Joined
Dec 24, 2011
Messages
3,155
ahhhh, ok, I knew I was overthinking it, thanks for clearing the cobwebs
 

Watch MrExcel Video

Forum statistics

Threads
1,109,464
Messages
5,528,957
Members
409,848
Latest member
Blomsten
Top