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: [email protected] 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
