countif question

[Mike Hyndman]

does the contif function have a problem with the < (less than symbol)? I
have a column of data which is supplied from a look up table and one of
the values is <3. I have tried to do a countif on the value <3 but it
will not work. As a workaround I am using the open bracket (3 instead
and it works fine. Why not with the < symbol.
puzzled
TIA
Mike H

 

[RagDyer]

Try this:

=COUNTIF(A1:A20,"<3")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


does the contif function have a problem with the < (less than symbol)? I
have a column of data which is supplied from a look up table and one of
the values is <3. I have tried to do a countif on the value <3 but it
will not work. As a workaround I am using the open bracket (3 instead
and it works fine. Why not with the < symbol.
puzzled
TIA
Mike H

 

[Arvi Laanemets]

Hi

Excel is helpful, and interpretes the condition "<3" as 'count numeric
values which are less than 3'
Instead try something like:
=COUNTIF(A1:A100,"=" & "<3")

Arvi Laanemets

 

[Mike Hyndman]

On Tue, 21 Sep 2004 22:53:05 +0300, "Arvi Laanemets"

Hola,
why the "=" and ampersand?
Mike H

 

[Mike Hyndman]

Wow, that was quick!
So are you saying that if I use the expression =countif(a1:a50,"<3") it
will count 2s and 1s.? So far I haven't seen any evidence of this. Aslo,
why can I not use countif to "count " occurances of the < symbol. This
is just a matter of interest. (Excel in Office XP, BTW)
Many thanks to you both for your prompt replies.
Mike H.

 

[RagDyer]

<"So far I haven't seen any evidence of this.">

The formula works as advertised.
Can't vouch for your data though!
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

Wow, that was quick!
So are you saying that if I use the expression =countif(a1:a50,"<3") it
will count 2s and 1s.? So far I haven't seen any evidence of this. Aslo,
why can I not use countif to "count " occurances of the < symbol. This
is just a matter of interest. (Excel in Office XP, BTW)
Many thanks to you both for your prompt replies.
Mike H.

 

[Arvi Laanemets]

Hi


In general condition must be enclosed into quote marks, i.e. it must be a
string. But you can use a simplified syntax for fixed values, where you omit
the operator, and don't need quotes for non-string conditions. So valid
expressions are both:
=COUNTIF(A:A,3)
(counts occurences of number 3 in column A);
and
=COUNTIF(A:A,"=3")
(does exactly the same as first one);
=COUNTIF(A:A,$B$1)
(also same as previous, when the value 3 is stored in B1).

Also are valid expressions
=COUNTIF(A:A,"3")
=COUNTIF(A:A,"=" & "3")
=COUNTIF(A:A,$B$1)
=COUNTIF(A:A,"=" & $B$1)
for counting string value "3" (for last formula stored in cell B1, formatted
as text).

(NB! COUNTIF counts 3's or "3"'s, but not both at same time. So when you
cell formats are messed up, you may get wrong results!)

Whenever you include a character used as operator into COUNTIF/SUMIF
condition, it's interpreted by Excel as operator - unless you use
concatenation. The condition "=" & "<3" in my formula is interpreted as
'equal to string "<3"'.

 

[Mike Hyndman]

Hi


In general condition must be enclosed into quote marks, i.e. it must be a
string. But you can use a simplified syntax for fixed values, where you omit
the operator, and don't need quotes for non-string conditions. So valid
expressions are both:
=COUNTIF(A:A,3)
(counts occurences of number 3 in column A);
and
=COUNTIF(A:A,"=3")
(does exactly the same as first one);
=COUNTIF(A:A,$B$1)
(also same as previous, when the value 3 is stored in B1).

Also are valid expressions
=COUNTIF(A:A,"3")
=COUNTIF(A:A,"=" & "3")
=COUNTIF(A:A,$B$1)
=COUNTIF(A:A,"=" & $B$1)
for counting string value "3" (for last formula stored in cell B1, formatted
as text).

(NB! COUNTIF counts 3's or "3"'s, but not both at same time. So when you
cell formats are messed up, you may get wrong results!)

Whenever you include a character used as operator into COUNTIF/SUMIF
condition, it's interpreted by Excel as operator - unless you use
concatenation. The condition "=" & "<3" in my formula is interpreted as
'equal to string "<3"'.

Arvi,

Many thanks for your explanation. I knew I needed to make the <3 a
string but had forgotten how.
regards
Mike H