Ask Your Question
0

Sum values in a column until the next empty cell [closed]

asked 2013-11-16 20:53:55 +0200

koliplay gravatar image

Hi everyone! I have exactly the same problem as mentioned here: http://www.mrexcel.com/forum/excel-questions/143813-sum-values-column-until-next-empty-cell.html Only that solution doesn't work for libreoffice - I just do not know how to make it work. Or is there another formula? No pivot table or makros please. Thanx a lot in advance.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-14 15:17:01.883451

1 Answer

Sort by » oldest newest most voted
1

answered 2013-11-16 22:13:18 +0200

DaveDixon gravatar image

This seems to work

=SUM(OFFSET(A2,0,0,MATCH(1,IF(A2:A100="",1,0),0),1))
edit flag offensive delete link more

Comments

Just want to note (as in the linked article) that this formula needs to be entered as an array (CTRL+SHIFT+ENTER).

oweng gravatar imageoweng ( 2013-11-16 22:43:02 +0200 )edit

Thank you Dave for such a prompt answer. Seems like I have to learn more basics of liboffice because this I can't get to work either. Maybe it is some setting issue? First it returned error #501 and after changing "," to ";" #VALUE! error...

koliplay gravatar imagekoliplay ( 2013-11-16 22:54:24 +0200 )edit

@koliplay, it should work but the given form will only do so in cell A1 with the list of values to sum beneath in cells A2 to A100 (broken at some point by a blank cell). It sounds like you are using semi-colon as a delimiter (which is more international) so the form =SUM(OFFSET(A2;0;0;MATCH(1;IF(A2:A100="";1;0);0);1)) is required.

oweng gravatar imageoweng ( 2013-11-17 11:18:25 +0200 )edit

Thanx to both of you - I've been entering it in A2 so as soon as I get back to calc I'm gonna give it another try. but I am sure it is solved.

koliplay gravatar imagekoliplay ( 2013-11-17 23:43:27 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-11-16 20:53:55 +0200

Seen: 8,762 times

Last updated: Nov 16 '13