Count cells based on multiple OR criteria

[David627]

I need to have multiple ranges count based on several criteria. The hitch is that needs to count if one or more criteria is met not all. I tried using a COUNTIFS however that seems to evaluate based on AND not OR.

Is there a nested COUNTIFS or similar function that would work?

 

[David627]

So it looks like the double-dashes act as OR actions

 

[texasalynn]

no the double dash works to change the values to 0 or 1 instead of true or false

edit: also see this
http://www.mrexcel.com/forum/showthread.php?t=385842

 

[David627]

well this works. Thanks. A follow-up - what if I want to find one option or another that are unrelated. For example in the same column I want to count the number of times a range says BLUE and the number of times a range says RED - so if red shows up 2 times and blue 7 the answer should be 9. This is an OR statement as well

 

[texasalynn]

the simplist would be to add two sumproducts together

<TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR><TD>=SUMPRODUCT(--(A1:A6=627),--(B1:B6="a"),--(c1:c6="Blue"))
+SUMPRODUCT(--(A1:A6=627),--(B1:B6="a"),--(c1:c6="Red"))
</TD></TR></TBODY></TABLE>

 

[David627]

you are combining 2 separate questions - thisis a Separate formula: I only want to count the number of times a range says BLUE and the number of times a range says RED (nothing to do with 627 & a). Your answer would give me more 9 in my example above

 

[texasalynn]

you are combining 2 separate questions - thisis a Separate formula: I only want to count the number of times a range says BLUE and the number of times a range says RED (nothing to do with 627 & a). Your answer would give me more 9 in my example above

Your post said it was a follow-up so was thinking it was still the same and I was trying to give you an example. So if it is a straight forward single type of count then countif would give you that??? Yes/No

 

[David627]

Can I use multiple criteria in COUNTIF? I thought it would only evaluate 1?

 

[texasalynn]

=countif(a1:a100,"Blue")+countif(a1:a100,"Red")

Excel Workbook
	A	B
2		Total Blue/Red
3	Blue	4
4	Red
5	Blue
6	Red
7	Orange
8	Pink
Data

 

[David627]

Thank you for all your assistance!

 

[texasalynn]

your welcome and another option

Excel Workbook
	A	B
2		Total Blue/Red
3	Blue	4
4	Red	4
5	Blue
6	Red
7	Orange
8	Pink
9
Data