# LibreOffice Calc. Number of nonempty cells. [closed]

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 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

Sort by » oldest newest most voted

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.

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.

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

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 5.0.0.3

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(com.sun.star.sheet.GeneralFunction.COUNT), 64, ""

flag =        com.sun.star.sheet.CellFlags.VALUE
flag = flag + com.sun.star.sheet.CellFlags.DATETIME
flag = flag + com.sun.star.sheet.CellFlags.STRING
'flag = flag + com.sun.star.sheet.CellFlags.FORMULA'

oResult = oCells.queryContentCells(flag)

if oResult.count = 0 then
msgbox "none", 64, ""
else
msgbox "Custom: " & oResult.computeFunction(com.sun.star.sheet.GeneralFunction.COUNT), 64, ""
end if


Empty.ods

Regards

more

All you need is the Calcformula:

=SUM(ISEMPTY( your_cell_range ))


as Arrayformula ctrl+shift+enter

more