Ask Your Question
0

Calc: calculate number of cells containing digits, rather than actual digits within each cell, in Calc? [closed]

asked 2019-10-03 03:36:51 +0100

appreciatethehelp gravatar image

Mac OS Mojave, LO 6.2.5.2

Let's say I have a spreadsheet containing 4 cells, in each cell is a number, let's say 1,2,3 & 4 for each respective cell. How do I calculate the sum of the number of cells with numbers in them (4), rather than the total of the numbers within the cells, to return the result 4, instead of 10 (as a regular SUM formula would produce)?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by appreciatethehelp
close date 2019-10-03 06:17:52.877090

1 Answer

Sort by » oldest newest most voted
0

answered 2019-10-03 03:59:18 +0100

Opaque gravatar image

updated 2019-10-03 04:04:00 +0100

Hello

assuming your numbers are in A1:A4 use: =SUMPRODUCT(ISNUMBER(A1:A4)) or {=SUM(ISNUMBER(A1:A4)) (edit without curly brackets and enter using CTRL+SHIFT+ENTER)

Tested on LibreOffice

Version: 6.3.2.2,Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kde5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded

Hope that helps.

edit flag offensive delete link more

Comments

The former produced the desired result. Many thanks indeed!

appreciatethehelp gravatar imageappreciatethehelp ( 2019-10-03 06:17:44 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2019-10-03 03:36:51 +0100

Seen: 30 times

Last updated: Oct 03