count + or function

Student for life

New Member
Joined
Sep 16, 2014
Messages
21
Hello everyone,
Does anyone know how to count cells that match either of multiple criteria? COUNTIFS formula is actually a count of cells that match more than one criteria -count with multiple AND in it- where as I'm looking for a formula with multiple OR.
I hope I've explained it clearly.
Thank you in advance.
 
Last edited:

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Add COUNTIF together, so if you want to know if column A has 1 or 3

=COUNTIF(A:A,1)+COUNTIF(A:A,3)
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
countifs does OR within reason so it really depends on how many OR's

=SUM(COUNTIFS(A2:A10,{"Mary","Joe"},B2:B10,{"1";"2"}))

so this would do 4 OR's, though note the semi-colon in the second one.

you may need to consider sumproduct if its overly complicated with multiple dates or something

A
B
C
1
Name
ID
2
Mary
1
7
3
Joe
1
4
Mary
1
5
Peter
1
6
Kate
2
7
Joe
2
8
Joe
2
9
Mary
2
Mary
2

<TBODY>
</TBODY>
 

Student for life

New Member
Joined
Sep 16, 2014
Messages
21

ADVERTISEMENT

Thank you Scott. This is a very simple and effective method. I appreciate your help.

P.S
Sorry I'm kind of new here. I just realized I have to individuals this way.
 

Student for life

New Member
Joined
Sep 16, 2014
Messages
21
OK I'm still stuck with this problem. I think that's because I didn't realized there was another condition that I had to count. Please let me explain from the top:

I have three columns and I need to count all the cells that meet condition X in column A then meet either condition Y in column B OR condition Z in column C. I can calculate the OR part by using coundif(columnB,conditionY)+countif(columnC, conditionZ)-coundifs(columnB,conditionY,columnC,conditionZ). But I don't know how to count the cells in column A that meet condition X . If you need me to show you all this is a sample table, please let me know how to draw or paste one here.
Thank you,
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Something like this:

=SUMPRODUCT(--(A1:A1000="X"),--(((B1:B1000="Y"))+(C1:C1000="Z")>0))
 

Student for life

New Member
Joined
Sep 16, 2014
Messages
21
Perfect! Worked like a charm. Thanks Scott. Not sure about the -- and the >0 part. Would you please explain what they do?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The -- coerces the TRUE/FALSE values to 1 and 0 respectively.

Adding TRUE or FALSE values together will coerce them to 1 and 0 as well so. If B1 was TRUE, that would be 1 and if C1 was TRUE that would be 1, added together is 2 which is greater than 0, if either one was true and the other false, that would total 1, the greater than 0 will return a TRUE/FALSE and the -- in front of that will coerce it to 1 or 0.
 

Student for life

New Member
Joined
Sep 16, 2014
Messages
21
Thanks for the explanation. I totally get the logic but have two quick questions about the formula. Do you need to have two parenthesis around column B =SUMPRODUCT(--(A1:A1000="X"),--(((B1:B1000="Y"))+(C1:C1000="Z")>0))? I took them off and nothing changed?

Also, am I correct assuming the double negative outside the B & C column
=SUMPRODUCT(--(A1:A1000="X"),--(((B1:B1000="Y"))+(C1:C1000="Z")>0)) is coercing the TRUE/FALSE for both of these columns at the same time? I moved the double negative inside the parenthesis and got a different value.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,922
Messages
5,525,647
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top