Problems with count, counta and countifs

[Homer Shannon]

I have a simple table, which is not so large that I can't count the numbersby hand, but I'm trying to generate a report, so I'd like to use the countcommands to create tallys by several different groups.

What I'm finding is that the results of using these commands are not consistent with what I can actually count by hand. I tried recreating the problems using some small samples and was able to recreate one of the problems: counta always returns a count one more than are actually present. What's withthat? For example, =counta(a1:a3,"*") returns an answer of 4! There are only three cells in the equation. If this is changed to =count(a1:a3,"*"), the answer is correct.

I am also using a countifs command. This is a bit more complex, so I can't cite an example so clearly, but here is what I am getting:

=COUNTIFS('Membership Information'!B:B,"Life",'Membership Information'!D,"*") returns an answer of 43, which is correct.

However, the similar command:

=COUNTIFS('Membership Information'!B:B,"Regular",'Membership Information'!D,"*") Returns an answer of 94 when I can actually count the correct answers and there are 98.

I'm also getting odd answers with the command:

=COUNT('Membership Information'!D,"*") The correct answer is 141 but Excel returns '4'. If I use the command =COUNTA('Membership Information'!D,"*")-1, I get the correct answer.


I went through all the empty boxes to be sure there was no hidden data, andthere isn't. Why is Excel returning these inconsistent answers?

 

[Claus Busch]

Hi Homer,

Am Wed, 11 Dec 2013 07:57:56 -0800 (PST) schrieb Homer Shannon:

I have a simple table, which is not so large that I can't count the numbers by hand, but I'm trying to generate a report, so I'd like to use the count commands to create tallys by several different groups.

What I'm finding is that the results of using these commands are not consistent with what I can actually count by hand. I tried recreating the problems using some small samples and was able to recreate one of the problems: counta always returns a count one more than are actually present. What's with that? For example, =counta(a1:a3,"*") returns an answer of 4! There are only three cells in the equation. If this is changed to =count(a1:a3,"*"), the answer is correct.

COUNT and COUNTA have no second argument.
=COUNT(A1:A3) counts the numeric values in range A1:A3
=COUNTA(A1:A3) counts all values in range A1:A3


Regards
Claus B.

 

[Homer Shannon]

I think Excel may have been hosed up. I shutdown and rebooted my machine and then exported the file out of Quickbooks again, this time using CSV instead of XLS format. The formulas are working much more consistently, counta is not giving me a +1 value and using a ">0" in the countifs works fine for finding entries in numeric fields. I have no idea what was going on the earlier.

 

[GS]

I think Excel may have been hosed up. I shutdown and rebooted my

machine and then exported the file out of Quickbooks again, this time
using CSV instead of XLS format. The formulas are working much more
consistently, counta is not giving me a +1 value and using a ">0" in
the countifs works fine for finding entries in numeric fields. I have
no idea what was going on the earlier.

Well that makes sense since the report exported to Excel comes in as
raw text (for the most part), though I believe numeric values are
formatted as such. Your calcs should be done on a separate sheet that
pulls the values from the exported report via the lookup functions. At
least, that's how I've been doing it for the past 10 years for monthly
summary presentations. The target Excel file is designed to amalgamate
figures from several different custom reports for the specified fiscal
period. So far this has been a reliable and accurate solution for many
clients.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Ron Rosenfeld]

=counta(a1:a3,"*") returns an answer of 4!

If A1:A3 contain data, then that is the correct result.

"*" is counted as a value by countA; you can have up to some number of arguments that varies depending on the Excel version, but in Excel 2007 it can be up to 255.

COUNTA is NOT COUNTIF, where the 2nd argument represents a criteria; it is merely another item to be counted.

 

[Ron Rosenfeld]

=COUNTIFS('Membership Information'!B:B,"Regular",'Membership Information'!D,"*") Returns an answer of 94 when I can actually count the correct answers and there are 98.

Probably some of the cells that you think contain "Regular" have leading or trailing spaces, so are not being counted.

 

[Ron Rosenfeld]

=COUNT('Membership Information'!D,"*") The correct answer is 141 but Excel returns '4'. If I use the command =COUNTA('Membership Information'!D,"*")-1, I get the correct answer.

That makes sense of 136 items of your membership information are Text and not numbers.