Search code examples
countrangeopenoffice.orgopenoffice-calccalc

How can a formula be used in scalc (Apache OpenOffice) to count cells with content, while the count should stop at the first empty cell?


Here (https://www.howtoexcel.org/formulas/how-to-find-the-position-of-the-first-non-blank-cell-in-a-range/) is a source for Excel where the following formula is described:

{=MATCH(FALSE,ISBLANK(B3:B9),0)}

The count should stop at b because the following cell is empty or contains no printable character. The result should be 2.

=COUNTA(A30:A50) ; A29
a
b

c

Solution

  • There is no need to use a count function because we can subtract 1 from the matched index to get the count. Also, to find the first blank cell, the search criterion should be true, not false.

    =MATCH(TRUE();ISBLANK(A30:A50);0)-1
    a
    b
    
    c
    

    Result: 2

    In LibreOffice Calc, this works when entered normally. In Apache OpenOffice, it must be entered as an array formula with Ctrl+Shift+Enter.