COUNTIFS with multiple criteria/variables and "OR"?

[bitchinmona]

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?

 

[Jonmo1]

Welcome to the board.

Try

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

 

[rob.barnes01]

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"))

 

[VoG]

Try like this

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

 

[Jeff Meyers]

You already have it here:

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")

 

[bitchinmona]

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.

 

[bitchinmona]

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.

 

[Jonmo1]

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)

 

[bitchinmona]

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.