Re: [libreoffice-users] Calc function COUNTA

At 21:41 25/06/2022 +0200, Harvey Nimmo wrote:
> I wanted to count the number of non-blank items in a list of items 
> using COUNTA. Cell A1 has the formula =COUNTA(A$2:A$10000, 
> ISBLANK=0) above an otherwise empty column.  It returns the value 1! 
> Is that supposed to mean something?

Yes: it's the correct result!

The big question is what you intend the formula to mean. ISBLANK() is 
a function, so makes no sense without the parentheses and a 
parameter. Did you expect that second parameter to COUNTA() to mean something?

What were you actually trying to achieve? Do you want your formula to 
omit cells containing just blanks in its count, as well as empty 
cells? If so, you need to construct a formula that achieves this. But 
you need to speak Spreadsheet, not just English, to do so! Try
=SUMPRODUCT(LEN(TRIM(A$2:A$10000))>0)

> It's obviously not a big problem, because a workaround is obvious.

I don't think it's a problem at all.

> But do I have to mistrust the results of the COUNTA function?

No. As others have said, if your specified range is empty, the first 
parameter contributes zero to the COUNTA() sum. But then the 
(apparently meaningless) second parameter contributes itself, so the 
total is indeed one.

I trust this helps.

Brian Barker



--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy