How to sum the next n non-empty cells in LibreOffice Calc

0 votes
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 by Kithuzzz
• 38,000 points
780 views

1 answer to this question.

0 votes

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

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 narikkadan
• 63,640 points

Related Questions In Others

0 votes
1 answer

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

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

answered Dec 24, 2022 in Others by narikkadan
• 63,640 points
773 views
0 votes
1 answer

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

answered Feb 7, 2023 in Others by narikkadan
• 63,640 points
764 views
0 votes
1 answer
0 votes
1 answer

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

answered Feb 23, 2023 in Others by Kithuzzz
• 38,000 points
498 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
3,902 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,970 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,690 points
961 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 23,260 points
3,625 views
0 votes
1 answer
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,640 points
770 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP