COUNTIFS with OR condition

Hi

I have 3 columns. I want to count the number of 1s (could actually be 1-/1/1+) in column A and the number of 1s in EITHER columns B and C

so for e.g. if cell A1 contained 1 and celll B1 contained 2 and cell C1 contained 1 ... then the count would return 1

and if cell A1 contained 1 and cell B1 contained 1 and cell C1 contained 2 ... then the count would still return 1

I think I would need something like

Please Login or Register  to view this content.

????? But would need an AND/OR condition for columns B and C.


Hope that makes sense and someone can help.

TIA

You should regard all paramaters in COUNTIFS as AND nor OR

use this instead
=COUNTIFS(A1:A40,1,B1:B40,1)+COUNTIFS(A1:A40,1,C1:C40,1)

NOTE: If A1 contains 1 and B1 contains 1 and C1 contains 1 this would count as 2 not 1 (1 for A and B, 1 for A and C)

or use

=SUMPRODUCT((A1:A40=1)*((B1:B40=1)+(C1:C40=1)))
which will produce 1 in the above NOTE

Formula:

Please Login or Register  to view this content.

The wildcard should be "1*".

This would work:

=COUNTIFS($A$1:$A$40,"1*",$B$1:$B$40,"1*",$C$1:$C$40,"1*")

only if there were a 1 in every cell in all three ranges, but I don't think that's really what you want. Are you wanting to do this without a helper column?

Originally Posted by technik

I only need a count of 1 is there was a 1 in all 3 columns.

So where would the OR come into it?

That's A is 1 AND B1 is 1 AND C is 1.