# COUNTIFS with multiple criteria/variables and "OR"?

#### bitchinmona

##### New Member
I'm trying to set up a contingency table where Excel (2007) tells me how many are X and Y or Z or A.

For example, Let's say I have a dataset of 100 bicycles, and I need to find out how many are Trek brand, and of those, which are red, blue, or silver.

I've figured out how to have it count how many are Trek and Red, then Trek and Blue, then Trek and Silver, but not how many are Trek and red or blue or silver.
I've tried doing this using the AND and OR logic parameters but I always end up with a value error.

So ultimately what I want to know is how many of the 100 bikes are Trek and either red, OR blue, OR silver.

Any ideas?

### Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Welcome to the board.

Try

=SUM(COUNTIFS(A1:A100,"Trek",B1:B100,{"red","blue","silver"}))

Can you just SUM your COUNTIFS?

=SUM(COUNTIFS(A1:A20,"Trek",B1:B20,"Red"),COUNTIFS(A1:A20,"Trek",B1:B20,"Blue"),COUNTIFS(A1:A20,"Trek",B1:B20,"Silver"))

Try like this

=SUMPRODUCT(--(A1:A100="Trek"),,--ISNUMBER(MATCH(B1:B100,{{Red","Blue"})))

I've figured out how to have it count how many are Trek and Red, then Trek and Blue, then Trek and Silver, but not how many are Trek and red or blue or silver.
Just add those 3 together in one cell. For instance:

=COUNTIFS(A:A,"TREK",B:B,"Red")+COUNTIFS(A:A,"TREK",B:B,"Blue")+COUNTIFS(A:A,"TREK",B:B,"Silver")

Thanks. I tried that, but I must have explained it wrong.

The way the dataset is arranged would be like follows:

So I want to see which are Trek, and are *either* red, blue, or silver. Or all three. I want to see how many Trek bikes have a YES in any of those columns.

Can you just SUM your COUNTIFS?

=SUM(COUNTIFS(A1:A20,"Trek",B1:B20,"Red"),COUNTIFS(A1:A20,"Trek",B1:B20,"Blue"),COUNTIFS(A1:A20,"Trek",B1:B20,"Silver"))

Ultimately I'll be using the data in a pivot table.. I'm fairly certain I'm overthinking this.

You said..

I've figured out how to have it count how many are Trek and Red, then Trek and Blue, then Trek and Silver, but not how many are Trek and red or blue or silver.

So, all you need is to add the 3 together
=(formula that counts Trek and Red)+(formula that counts Trek and Blue)+(formula that counts Trek and Silver)

You said..

So, all you need is to add the 3 together
=(formula that counts Trek and Red)+(formula that counts Trek and Blue)+(formula that counts Trek and Silver)

Derp. Thanks! I think that might be what I'm looking for.

Replies
4
Views
139
Replies
4
Views
114
Replies
5
Views
413
Replies
4
Views
297
Replies
0
Views
43

1,202,987
Messages
6,052,928
Members
444,615
Latest member
bmwm3cj20

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