Excel Bugs

[rguerrie]

Excel is counting incorrectly, I think. I am using counta to count
the number of Xs in a column. If I enter counta(a1:a23,"x") it
evaluates to 1, which is incorrect. If I enter counta(a1:a23) it
evaluates to zero, which is correct. To verify this I search/find a1
to a23 for x, it says there aren't any. I also visually inspect the
column and there are no Xs. It does this in a number of columns.
It also miscounts 1 X as 2 Xs. When there is more than 1 X in a
column, it appears to count correctly.

My spreadsheets aren't that big, less than a MB each. This is not the
first problem I have encountered. When I copy then past special--
values only and transpose, it sticks in extra cells containing zeros.
I get a lot of buggy stuff like this. Why do I not see other people
complaining about things like this? I am a fairly sophisticated
computer user. Can anyone tell me if I am doing something wrong.

 

[rguerrie]

On further examination, every counta, including those columns with
more than 1 X is counting an additional x that isn't there.

 

[T. Valko]

You're using the wrong function for the task you want to achieve.

Try it like this:

=COUNTIF(A1:A23,"x")

COUNTA returns the total count of its arguments that *are not empty*.

=COUNTA(A1:A23,"x")

That formula contains 2 arguments: A1:A23 and the text string "x".

If A1:A23 is completely empty the count from that range (argument) is 0. The
second argument is the text string "x" which is "not an empty argument" so
it counts as 1.

So:

=COUNTA(A1:A23,"x") = 1
=COUNTA(A1:A23,"x","y","z") = 3

Which is correct!

 

[David Biddulph]

Yes, what you are doing wrong is not understanding the function you are
using. I would have hoped that as "a fairly sophisticated computer user"
you might perhaps know how to use Excel's Help function to tell you what the
COUNTA function does, before starting to complain about "bugs". You may
perhaps have intended to use COUNTIF?

 

[rguerrie]

I will certainly switch to countif.

However, in the help page for COUNTA, the following example is given:

=COUNTA(A1:A7,"Two") Counts the number of nonblank cells in the list
above and the value "Two"

It also describes counta as "Counts the number of cells that are not
empty and the values within the list of arguments. "

Perhaps I am not seeing something here? If counta(a1:a23) evaluates
to zero, shouldn't counta(a1:a23,"x") (with or without the quotation
marks--same results) also evaluate to zero?

I appreciate your help.

 

[rguerrie]

Hmmm.
Can you give me an example of when counta(a1:a23,"x") might be used,
then?

 

[rguerrie]

Excel is counting incorrectly, I think. I am using counta to count
the number of Xs in a column. If I enter counta(a1:a23,"x") it
evaluates to 1, which is incorrect. If I enter counta(a1:a23) it
evaluates to zero, which is correct. To verify this I search/find a1
to a23 for x, it says there aren't any. I also visually inspect the
column and there are no Xs. It does this in a number of columns.
It also miscounts 1 X as 2 Xs. When there is more than 1 X in a
column, it appears to count correctly.

My spreadsheets aren't that big, less than a MB each. This is not the
first problem I have encountered. When I copy then past special--
values only and transpose, it sticks in extra cells containing zeros.
I get a lot of buggy stuff like this. Why do I not see other people
complaining about things like this? I am a fairly sophisticated
computer user. Can anyone tell me if I am doing something wrong.

 

[rguerrie]

Interestingly, this doesn't account for the cut and paste error.
Other people here have run into similar problems. I do appreciate your
help.

Anyway, I am relieved to see that counta was not the correct choice,
even if that was not immediately obvious.

 

[rguerrie]

Hmmm.
Can you give me an example of when counta(a1:a23,"x") might be used,
then?

 

[rguerrie]

PS, I am a fairly sophisticated computer user. However I am a fairly
naive Excel user. I am more than willing to admit that--which is why
I asked for help, instead of just writing it off as a bug.

 

[Ron Rosenfeld]

Perhaps I am not seeing something here? If counta(a1:a23) evaluates
to zero, shouldn't counta(a1:a23,"x") (with or without the quotation
marks--same results) also evaluate to zero?

No, because COUNTA is also evaluating "x" as a separate argument. For example,

=COUNTA(A1:A23,x,y) will evaluate to 2, with nothing in A1:A23
--ron

 

[T. Valko]

I can't think of a practical situation where one would use that but you
might use something like this:

=COUNTA(A1:A23,B1)

 

[David Biddulph]

In the example from the help page, there are 6 non-blank cells in the A1:A7
range, and adding the the value "Two" to the list of arguments brings the
result up to 7 as given in the example.

In your example, if COUNTA(A1:A23) comes to zero, COUNTA(A1:A23,"x") gives a
total of 1 because you are adding COUNTA("x") which would be 1. Your list
of arguments is (A1:A23,"x"), so you are counting the number of non-blank
entries in that list, and that number is 1 as "x" is non-blank.