Re: [libreoffice-users] Calc function COUNTA

Thanks for all the inputs! Great stuff.

As I said, I wanted to count the number of non-blank cells in a column,
but as =COUNTA(A$2:A$10000, ISBLANK=0) is above an otherwise empty
column returns the value 1, I am obviously not using the function
correctly. =COUNTA(A$2:A$10000) does the trick. Thanks.

Cheers
Harvey 




On Sun, 2022-06-26 at 06:40 +0100, Brian Barker wrote:
> 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