Ask Your Question

LibreOffice Calc. Number of nonempty cells. [closed]

asked 2015-07-25 15:08:20 +0100

AnEagle gravatar image

How in LibreOffice Calc obtain a number of nonempty cells? Let's say that we have a cell range called cells, then the question is:

int i = 0;
for (Cell cell : cells)
    if (!cell.isEmpty()) ++i;
return i;

How to do it with LibreOffice formula language or how to import own macros in any language? I need platform independent solution.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-23 08:36:43.506908

3 Answers

Sort by » oldest newest most voted

answered 2015-07-25 17:23:01 +0100

Lupp gravatar image

updated 2015-07-25 17:50:35 +0100

This depends on what you call a "nonempty cell". If this is a cell, say A1, answering TRUE being asked =A1<>"" then you may use for A1:A10 e.g. {=COUNTIF(A1:A10<>"";TRUE())} as an array formula OR =SUMPRODUCT(A1:A10<>"").

If you are talking of cells answering TRUE being asked =NOT(ISBLANK(A1)) the respective formula =SUMPRODUCT(NOT(ISBLANK(A1:A10))) should do. presently won't work because array evaluation is not applicable to ISBLANK.

(Current versions obviously support array evaluation for ISBLANK as I just tested.)

EDITING: I remembered wrongly. Just tested with "legacy" version 3.3.4 of LibreOffice and it also supported everything.

edit flag offensive delete link more


1) For completeness: I suspected ISBLANK of not being elegible for implicit iteration in an array formula. In fact the function actually not permitting this kind of evaluation was ISFORMULA.

3) The much better solution in the second case is the one @pierre-yves samyn suggeted.

Lupp gravatar imageLupp ( 2015-07-26 23:30:21 +0100 )edit

answered 2015-07-25 17:50:50 +0100

pierre-yves samyn gravatar image

Hi - some comments...

Why not simply =COUNTA(A1:A10)

This =SUMPRODUCT(A1:A10<>"") helps to differentiate cells containing a formula but showing no results for instance.

And =SUMPRODUCT(NOT(ISBLANK(A1:A10))) works with the test version

But as the question is tagged Script you can do in several ways. For example, the first simple, the second allowing more detail:

dim oSheet as object, oCells as object
dim oResult
dim flag as long

oSheet = thiscomponent.sheets.getByName("Sheet1")
oCells = oSheet.getCellRangeByName("A1:A10")

msgbox "Not empty: " & oCells.computeFunction(, 64, ""

flag =
flag = flag +
flag = flag +
'flag = flag +'

oResult = oCells.queryContentCells(flag)

if oResult.count = 0 then
    msgbox "none", 64, ""
    msgbox "Custom: " & oResult.computeFunction(, 64, ""
end if



edit flag offensive delete link more

answered 2015-07-25 17:20:33 +0100

karolus gravatar image

All you need is the Calcformula:

=SUM(ISEMPTY( your_cell_range ))

as Arrayformula ctrl+shift+enter

edit flag offensive delete link more

Question Tools



Asked: 2015-07-25 15:08:20 +0100

Seen: 26,909 times

Last updated: Jul 25 '15