COUNTIF Function Failure

[Geof Carrington]

COUNTIF works fine with the "apples" and "pears" in the
help example but fails (for me) if I'm trying to count
availabilities - "Y", "N" or "?".
=COUNTIF(I4:I156,"Y")
Generalising suggests that it doesn't like a single
character but I'm too lazy to go on trying.
Who knows a way round?

Thanks.

 

[Anon]

COUNTIF works fine with the "apples" and "pears" in the
help example but fails (for me) if I'm trying to count
availabilities - "Y", "N" or "?".
=COUNTIF(I4:I156,"Y")
Generalising suggests that it doesn't like a single
character but I'm too lazy to go on trying.
Who knows a way round?

Thanks.

COUNTIF works fine with single characters. For "Y" and "N" you must be doing
something else wrong. For example, it will only find exact matches, so if
you had (say) a trailing space "Y " this would not be counted.

With "?" it's a little more difficult, as this is a wildcard character, so
=COUNTIF(I4:I156,"?")
will count all instances of any single character. To count just instances of
"?", you must use
=COUNTIF(I4:I156,"~?")

 

[Geof Carrington]

-----Original Message-----
COUNTIF works fine with the "apples" and "pears" in the
help example but fails (for me) if I'm trying to count
availabilities - "Y", "N" or "?".
=COUNTIF(I4:I156,"Y")
Generalising suggests that it doesn't like a single
character but I'm too lazy to go on trying.
Who knows a way round?

Thanks.
.
I forgot to say I'm on Office 2K.

Thanks to respondees so far but, alas, telling me that it
works doesn't help. It doesn't work either.

The acceptable entries in the range are indeed "Y", "N",
or "?" and they all simply leave the uncalculated formula
sitting there looking at me. There are no extraneous
spaces or other characters and, in the case of "?", it
just doesn't count anything at all. "~?" fails to work
exactly the same as "?".

Ouch!

Geof

 

[Anon]

Thanks to respondees so far but, alas, telling me that it
works doesn't help. It doesn't work either.

The acceptable entries in the range are indeed "Y", "N",
or "?" and they all simply leave the uncalculated formula
sitting there looking at me. There are no extraneous
spaces or other characters and, in the case of "?", it
just doesn't count anything at all. "~?" fails to work
exactly the same as "?".

Ouch!

Geof

When you say: "...the uncalculated formula sitting there looking at me", do
you mean that you can see "=COUNTIF(I4:I156,"Y")" in the cell itself (not
just the formula bar)? If so, you have probably tried to enter a formula
into a cell formatted as text, so it isn't a formula at all. If this is the
case, format the cell as General, then click in the formula bar and press
ENTER.

 

[Alan Beban]

Anon wrote:
"For example, it [COUNTIF] will only find exact matches . . . ."

Not exactly. =COUNTIF(whateverrange,"d") will count both d and D.

Alan Beban

 

[Geof Carrington]

-----Original Message-----


When you say: "...the uncalculated formula sitting there looking at me", do
you mean that you can see "=COUNTIF(I4:I156,"Y")" in the cell itself (not
just the formula bar)? If so, you have probably tried to enter a formula
into a cell formatted as text, so it isn't a formula at all. If this is the
case, format the cell as General, then click in the formula bar and press
ENTER.


.
Thanks Anon

I think you've fixed it for me although I would point out
that I have been working the whole time in the same cell,
formatted as text and the original formula using "Y"
failed while the revised formula using "Yes" worked (and
still works) even while the text format still applied!! I
would also point out that other formulae, e.g. COUNTBLANK
work perfectly in the same cell, still with a text format.
(I started by using the total number of cells in the array
minus the blanks to determine the total number of entries
and then got more ambitious.)

 

[Anon]

I think you've fixed it for me although I would point out
that I have been working the whole time in the same cell,
formatted as text and the original formula using "Y"
failed while the revised formula using "Yes" worked (and
still works) even while the text format still applied!! I
would also point out that other formulae, e.g. COUNTBLANK
work perfectly in the same cell, still with a text format.
(I started by using the total number of cells in the array
minus the blanks to determine the total number of entries
and then got more ambitious.)

Glad the problem is fixed.

Cells formatted as text AFTER a formula is entered merely give the result
formatted as text. However, entering or editing the formula AFTER the cell
is formatted means that you enter text, not a formula. This is somewhat
confusing!

The best advice is probably to keep away from formatting as text except
where it is necessary (for example, entering what would otherwise be
interpreted as a number/date). I can't see why it would ever be necessary to
format as text a cell containing a formula.

 

[herman]

Are you trying to count "?"'s as well as "N"'s and "Y"'s

 

[Geof Carrington]

-----Original Message-----


Glad the problem is fixed.

Cells formatted as text AFTER a formula is entered merely give the result
formatted as text. However, entering or editing the formula AFTER the cell
is formatted means that you enter text, not a formula. This is somewhat
confusing!

The best advice is probably to keep away from formatting as text except
where it is necessary (for example, entering what would otherwise be
interpreted as a number/date). I can't see why it would ever be necessary to
format as text a cell containing a formula.


.
Me neither!