returned calculation incorrect

[Gotroots]

The following formula returns a total of 53 when in fact it should be 39

=COUNTA(AL10:AL8002)+'other additions'!AK5

puzzled

 

[David Biddulph]

I'll put money on the fact that the calculation is *not* incorrect.

What is the prize for whoever guesses what the content of the various cells
is, as you haven't told us?

The starting point is to split your formula into manageable chunks.
Does =COUNTA(AL10:AL8002) give you the value you expect?
If not, have you considered whether some of the cells may contain a text
string which might only contain spaces?
If in doubt, try a helper column with =LEN(AL10) and copy down and see how
many non-zero values you have.

 

[Mattlynn via OfficeKB.com]

I would double check the formula or post some details with the data of the
cell ranges
53 could well be correct !

Thanks ;-)

 

[Mattlynn via OfficeKB.com]

The Prize is mine !!

I'll put money on the fact that the calculation is *not* incorrect.

What is the prize for whoever guesses what the content of the various cells
is, as you haven't told us?

The starting point is to split your formula into manageable chunks.
Does =COUNTA(AL10:AL8002) give you the value you expect?
If not, have you considered whether some of the cells may contain a text
string which might only contain spaces?
If in doubt, try a helper column with =LEN(AL10) and copy down and see how
many non-zero values you have.

 

[Don Guillett]

Perhaps the "dreaded space bar" has been touched

=SUMPRODUCT((LEN(TRIM(M1:M21))>0)*1)

 

[Mike H]

Hi,

Not enough information but COUNTA will count all populated cells in the range.

Mike

 

[Jacob Skaria]

You must be having formulas in the range which return blank entries...Try one
of these formulas

=COUNTIF(AL10:AL8002,"?*")+COUNT(AL10:AL8002)
=SUMPRODUCT(--(TRIM(AL10:AL8002)<>""))

If this post helps click Yes