COUNTIFS with OR condition

[technik]

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

[Special-K]

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

[technik]

Thanks Special-K

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

As the values can actually be 1-/1/1+ (see post 1) then assuming I am to use a wildcard?

[Shareez Saleem]

Please Login or Register  to view this content.

[AliGW]

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?

[Special-K]

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.

[technik]

Hi

Just re-read my post and realised I mistyped. Apologies.

I meant to say only need a count if 1* in column A and a 1* in EITHER columns B and C. So using your second piece of code in post 2 but was asking about the wildcard.

Apologies again- long day.

[reachharry]

Hi,

Hope this helps
=SUMPRODUCT((LEFT(A2:A6,1)="1")*((LEFT(B2:B6,1)="1")+(LEFT(C2:C6,1)="1")))-COUNTIFS(A2:A6,1,B2:B6,1,C2:C6,1)