Ask Your Question
0

LibreOffice Calc. Number of nonempty cells.

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

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 close merge delete

3 Answers

Sort by » oldest newest most voted
0

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

Lupp gravatar image

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

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

Comments

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 +0200 )edit
0

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

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

edit flag offensive delete link more
0

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

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
Login/Signup to Answer

Question Tools

2 followers

Stats

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

Seen: 20,227 times

Last updated: Jul 25 '15