Counting cells

classic Classic list List threaded Threaded
5 messages Options
JOE CONNER JOE CONNER
Reply | Threaded
Open this post in threaded view
|

Counting cells

I am confused about how to count a number of cells that have a number.
In Calc, if cells a1:a11 each may or may not have a number in it, what
formula may I use to count how many cells have a number? I do not want
the sum of the numbers, just how many cells contain a number.

Thank you.


--
To unsubscribe e-mail to: [hidden email]
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
Natale Tarantino Natale Tarantino
Reply | Threaded
Open this post in threaded view
|

Re: Counting cells

Hi,
the countif function may be just for you
N.
Il 25/03/2021 04:46, Joe Conner ha scritto:
> I am confused about how to count a number of cells that have a number.
> In Calc, if cells a1:a11 each may or may not have a number in it, what
> formula may I use to count how many cells have a number? I do not want
> the sum of the numbers, just how many cells contain a number.
>
> Thank you.
>
>


--
To unsubscribe e-mail to: [hidden email]
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
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Counting cells

In reply to this post by JOE CONNER
Just did a quick test. Put 1 to 5 in A1 to A5, A to D in A6
to A9, left A10 and A11 blank.
=count(a1:a11) gives 5
=counta(a1:a11) gives 9

Countif could do other variations.  

On 24 Mar 2021 at 21:46, Joe Conner wrote:

To:             [hidden email]
From:           Joe Conner <[hidden email]>
Subject:         [libreoffice-users] Counting cells
Date sent:       Wed, 24 Mar 2021 21:46:34 -0600

> I am confused about how to count a number of cells that have a number.
> In Calc, if cells a1:a11 each may or may not have a number in it, what
> formula may I use to count how many cells have a number? I do not want
> the sum of the numbers, just how many cells contain a number.
>
> Thank you.
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor
(Retired)    
 mailto:[hidden email]                            
 mailto:[hidden email]
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




--
To unsubscribe e-mail to: [hidden email]
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

Andrew Pitonyak Andrew Pitonyak
Reply | Threaded
Open this post in threaded view
|

Re: Counting cells

In reply to this post by JOE CONNER
Not near a computer to test but I think you want the countif function

⁣Get BlueMail for Android ​

On Mar 25, 2021, 5:13 AM, at 5:13 AM, Joe Conner <[hidden email]> wrote:

>I am confused about how to count a number of cells that have a number.
>In Calc, if cells a1:a11 each may or may not have a number in it, what
>formula may I use to count how many cells have a number? I do not want
>the sum of the numbers, just how many cells contain a number.
>
>Thank you.
>
>
>--
>To unsubscribe e-mail to: [hidden email]
>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

--
To unsubscribe e-mail to: [hidden email]
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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

Re: Counting cells

In reply to this post by JOE CONNER
At 21:46 24/03/2021 -0600, Joe Conner wrote:
>I am confused about how to count a number of cells that have a
>number. In Calc, if cells a1:a11 each may or may not have a number
>in it, what formula may I use to count how many cells have a number?
>I do not want the sum of the numbers, just how many cells contain a number.

This all depends on what you mean by "a number"! Do you just mean
that the cell has something in it, so presumably a number - just
anything, i.e. that the cell is not empty? If you mean specifically a
number, do you include an explicit zero? And how about a cell that
has a blank or blanks, which would be interpreted the same way as
zero in many calculation contexts? Note that a cell containing blanks
is different from an empty cell; you may or may not wish to include
such cells. You probably won't want to include text strings such as
"three", even though that is a number in one sense; but how about a
cell containing a number expressed as digits but in the form of a
text string, e.g. the character "3" as distinct from the numerical
value 3? (This may arise from entering a number into a cell formatted
as Text or by entering a leading apostrophe - or by pasting material
from elsewhere.)

The COUNT() function ignores empty cells and those that contain text,
so may well be all you need.

Otherwise you may like to consider formulae involving COUNTA() or
COUNTIF(). Note also that it is sometimes easier to calculate the
inverse of something, so you could consider using COUNTBLANK() (which
should really be called COUNTEMPTY(), since it fails to include cells
containing explicit blank spaces), and subtracting the result from
the number of cells in the range.

Exactly what you choose to do depends on how resilient you want your
spreadsheet to be to rogue or erroneous cell contents as a result of
later errors on your part (we all make them) or on the part of others
who may use the spreadsheet.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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