My LibreOffice Calc spreadsheet has cells with values in an atypical pattern in one column, which means that any number of empty or non-empty cells may follow a non-empty cell.

How can I add the following n (for example, 3) non-empty cells?

I discovered that the COUNTA function, an IF condition, and a dynamic range will likely require the SUM function. I can't seem to put it all together, though. With =SUM(B2:INDEX(B:B,COUNTA(B:B)) for instance, I can add up all non-empty rows in B, but how do I narrow this down to the following 3 non-empty entries?
Jan 30, 2023 in Others 536 views

1 answer to this question.

Here's a volatile solution (assuming you don't have more than 10k rows):

The array formula in G2 is

```=SUM(INDEX(B2:\$B\$10000,AGGREGATE(15,6,(1/ISNUMBER(B2:\$B\$10000))*(ROW(B2:\$B\$10000)-(ROW(B2)-1)),ROW(INDIRECT("1:"&D2)))))
```

(you could possibly reduce volatility by hard-coding the array constant - instead of using INDIRECT() - but I'm not very familiar with all of LibreOffice's volatile functions)

answered Jan 30, 2023 by
• 63,420 points

In excel 365 how to subtract values sequentially but skip empty cells

Try the following formula in C4 and ...READ MORE

How to divide data in excel into 4 columns whose sum is almost equal to 1/4 of the sum of all values/

5049 is the sum of all numbers, ...READ MORE

VBA Copy/Paste a Range in Next Available Column Then Add a Single Day to One of the Newly Pasted Cells Repeatedly

With a date you can treat it ...READ MORE

How to compare 2 cells with delimited items in each and output the difference in items?

The following function would do this for ...READ MORE

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE