Countif, multiple criteria

[JS25]

Hi all,

I'd really appreciate help with what is, I'm sure, a simple problem that I'm
too stupid to solve!

The problem - Using COUNTIF (I think), the problem is that it requires two
criteria/conditions to be met:

For the range to be counted (as 1, NOT 2), A1 must have a value of 0 AND B1
must be more than 1. ONLY if these conditions are meet should the range be
counted as 1.

Eagerly awaiting for reply,
JS25

 

[Mike H]

Hi,

Perhaps we could see a small amount of test data and the result you expect.

Mike

 

[JS25]

Certainly:

'A' 'B' Expect Result -
0 0 0
0 1 0
0 2 1
0 3 1
1 0 0
1 1 0
1 2 0
1 3 0
2 0 0
2 1 0
2 2 0
2 3 0

Basically, the two conditions are that 'A' must be 0, and 'B' must be more
than 1.

 

[T. Valko]

Try this:

=--AND(COUNT(A1:B1)=2,A1=0,B1>1)

Copy down as needed.

 

[JS25]

That is great, thank you so much You're a genius, bravo bravo

Once again, I thank you!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Shane Devenshire]

Hi,

The formula for your third column would be

=--AND(A1=0,B1>1)

for an overall count:

=SUMPRODUCT(--(A1:A12=0),--(B1:B12>1))

or if you are using 2007:

=COUNTIFS(A1:A12,0,B1:B12,">1")

--

If this helps, please click the Yes button.

Cheers,
Shane Devenshire