count + or function

Student for life

New Member
Joined
Sep 16, 2014
Messages
23
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:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Add COUNTIF together, so if you want to know if column A has 1 or 3

=COUNTIF(A:A,1)+COUNTIF(A:A,3)
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0
Something like this:

=SUMPRODUCT(--(A1:A1000="X"),--(((B1:B1000="Y"))+(C1:C1000="Z")>0))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top