=COUNTIF(B6,{">0","<5"}) has error

  • Thread starter Thread starter 0-0 Wai Wai ^-^
  • Start date Start date
0

0-0 Wai Wai ^-^

Hi.
=COUNTIF(B6,{">0","<5"})

When I type a value which is larger than 1, it always returns the count as 1.
When I type a value which is 0 or lower, it always return the count as 0.

What's wrong?
I suppose it should work like that the formula will consider 2 criteria.
 
Hi,
=COUNTIF(B6,{">0","<5"})
I suppose it should work like that the formula will consider 2 criteria.
Click to expand...

Nope! This formula returns a two-memeber array

{CountOfValuesHigherThanZero, CountOfValuesLowerThanFive}

In other words, your two criteria do not work together and are evaluated
separately. Consequently you will always see the first member of the array
in the cell, i.e. the result of the evaluation of the first condition.

You can see all that with your own eyes, if you select the cell with the
formula, clic inside the formula bar, select the whole formula and press F9.
Another way of checking this are these formulae:

=INDEX(COUNTIF(B6,{">0","<5"}),1)
=INDEX(COUNTIF(B6,{">0","<5"}),2)

Regards,
KL
 
What's wrong is that COUNTIF() *won't* work that way.

Try:

=COUNTIF(B6,">0") - COUNTIF(B6,">=5")

or, more efficiently:

=(B6>0)*(B6<5)
 
Thanks for all your helps.

By the way, what topics/techniques should I read in order to learn how to input:
- more than 1 cell reference in different formulas
- more than 1 criterion in different formulas
-- use AND, OR to link each criteria (eg criterion1 AND criterion2 OR
criterion3)

Currently, it seems different formulas have different ways to achieve the above.
(eg if I use countif, I need to do in XX way. If I use sunif, I need to do it in
YY way, and so on).
It would be great if I can learn the technique to do the above, so I don't need
to remember each by each rigidly.
 
How about if I wish to do the following:
=countif(TargetCell, Criterion1 OR [Criterion2 AND Criterion3])

Thanks.
 
Back
Top