2022 Archives by date, by thread · List index

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/