# count + or function

#### Student for life

##### New Member
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.

Last edited:

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Add COUNTIF together, so if you want to know if column A has 1 or 3

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

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>

Thank you Scott and Weazel. These are both great. I appreciate your help.

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.

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,

Something like this:

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

Perfect! Worked like a charm. Thanks Scott. Not sure about the -- and the >0 part. Would you please explain what they do?

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.

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.

Replies
6
Views
224
Replies
3
Views
313
Replies
3
Views
290
Replies
8
Views
420
Replies
4
Views
207

1,214,720
Messages
6,121,124
Members
449,014
Latest member
Lilcps

### 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.

### Which adblocker are you using?

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

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