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 in Others 137 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)

• 59,740 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

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