count + or function

[Student for life]

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.

 

[Aladin Akyurek]

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.

Could be written as:

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

 

[Scott Huish]

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?

No. The B1:B1000="Y" will return a TRUE or FALSE value. The C1:C1000="Z will return a TRUE/FALSE value. For purpose of this explanation, I will just use ROW 1.

If B1 is equal to Y, that will return TRUE. If C1="Z" that will return TRUE.
These two TRUE/FALSE values are added together. Performing math on boolean values will coerce them to 1 for TRUE and 0 for false. So both true would be 1+1 or 2. We are then making another comparison to see if this value is greater than 0. That expression will return TRUE/FALSE. The -- on the outside of this is coercing that final TRUE/FALSE value to 1 or 0.

 

[Student for life]

Oh that is beautiful! I get it now. Thanks for the help. Have a wonderful weekend friends.