[Solved] SUM until next blank cell

Discuss the spreadsheet application

[Solved] SUM until next blank cell

Postby Ssronica » Thu Jan 02, 2020 7:21 am

I've researched this without much success. The only thing close to a solution was intended for Excel, which I'm unfamiliar with.

I need a formula which recognises a non-empty cell, then adds the numbers above it until the next blank cell. Getting OpenOffice to recognise the cell below is the easy part. I'm looking for something like this:

=IF(I28>1;IF(I29="";SUM(I28:[next blank cell];"");"")

Any help greatly appreciated!
Attachments
sample.ods
(19.07 KiB) Downloaded 25 times
Last edited by Ssronica on Fri Jan 03, 2020 3:39 pm, edited 3 times in total.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUM until next blank cell

Postby Zizi64 » Thu Jan 02, 2020 7:42 am

Do you mean "next blank cell" as the "last non-empty cell", or are there more than one blank (really blank!) cells in the specific cell range?

Please upload your ODF type sample file here with some sample data.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9580
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: SUM until next blank cell

Postby Ssronica » Thu Jan 02, 2020 9:06 am

Hi Zizi,

Thanks for responding. I've done as suggested.

The last block of data pertains to my initial question. I would like to get it to add from I30 upward to I26 and stop when it finds no data.

Thanks again.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUM until next blank cell

Postby Zizi64 » Thu Jan 02, 2020 9:19 am

Here is a sample code for a macro solution:

viewtopic.php?f=9&t=31021
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9580
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: SUM until next blank cell

Postby Ssronica » Thu Jan 02, 2020 9:26 am

Thanks for that.

Could you help me implement it? I've never used a macro before.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUM until next blank cell

Postby Lupp » Thu Jan 02, 2020 1:55 pm

(Please edit your original question and append the "above" to the subject if conjecture '0.' is correct.)
Having had a look into the sample I need to know if the original questioner probably
0. actually means "...then adds the numbers above it until the next blank cell.
1. wants to get subtotals per date. (column B)
2. insists on spliting columns by blank rows and assures to have done so for every section. (Originally supposed to be bad style.)
3. intends to scale up the sheet to thousands of rows. (Efficiency!)
4. needs a way to only calculate the sum for last rows of any section delimited by empty rows.
5. is considering to omit overblown formatting. (Simpler often is clearer and gives good survey.)

If "no" to 1., "yes" to 2., and "no" again to 3.
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(OFFSET(I30;MAX(ROW(I$1:I30)*(I$1:I30=""))-ROW(I30)+1;0;ROW(I30)-MAX(ROW(I$1:I30)*(I$1:I30=""));1))
is a solution without a macro. Of course, the subexpression
Code: Select all   Expand viewCollapse view
MAX(ROW(I$1:I30)*(I$1:I30=""))-ROW(I30)
should be outsourced for array evalution to a helper cell for the sake of efficiency, anyway.
Assuming the helper cell is P30, the original formuala would alter to
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(OFFSET(I30;P30+1;0;-P30;1))

An example about how I would do it (under the already mentioned set of conditions + "yes" to 4.) is attached.
Attachments
aoo100598specialSubtotals_1.ods
(24.05 KiB) Downloaded 15 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2929
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: SUM until next blank cell

Postby Villeroy » Thu Jan 02, 2020 2:51 pm

Pivot tables are easy. No macros. No formulas. Just maintain a clean data structure.
Attachments
aoo100598specialSubtotals_Pivot.ods
(19.58 KiB) Downloaded 14 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM until next blank cell

Postby Ssronica » Thu Jan 02, 2020 3:10 pm

Thanks all you guys.

I should have been cleared - the spreadsheet is being used for small business purposes. The empty rows are to delineate different purchases from suppliers. I would rather have identified the separate purchases with a different coloured background, but from reading other posts here on the forums I gather that there's no way to get OO to add based on different colours.

Lupp, thanks for that, but it didn't work for me. The answers to your questions were:

1. No, per purchase (there might be multiple items from different suppliers per date)
2. Yes, unless there's another way to force OO to stop adding.
3. Yes, perhaps scaled up in the future.
4. Yes - this is the core of my issue.
5. I'll consider anything as long as I can implemnent it at noob level (me = noob).

Thanks, again.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUM until next blank cell

Postby Villeroy » Thu Jan 02, 2020 3:24 pm

Mark the purchases with the invoice number, number of delivery note, delivery date or whatever. The gaps are a bad idea. You can not even sort that list.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM until next blank cell

Postby John_Ha » Thu Jan 02, 2020 3:25 pm

Are these the answers you want where you are summing over the red lines?

If not please edit your file and show the results you want.
Attachments
Clipboard03.gif
Clipboard03.gif (10.49 KiB) Viewed 711 times
Last edited by John_Ha on Thu Jan 02, 2020 3:28 pm, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7825
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: SUM until next blank cell

Postby Ssronica » Thu Jan 02, 2020 3:27 pm

John_Ha,

Yes, that's exactly what I'm looking for.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUM until next blank cell

Postby John_Ha » Thu Jan 02, 2020 3:41 pm

This is half way to what you want but very dangerous so do not use as it is because it relies on the blank cells being empty - even a space gives wrong answers. I set N6 to zero and then sum the values in column M into column N, putting the sum in column N to zero at every blank cell.

Is there a better cell you can guarantee will be "uncorrupted" to use as the decider? It should be easy to suppress the unwanted values - perhaps by hiding this column.

Code: Select all   Expand viewCollapse view
=IF(M11=0;" ";N10+M11)

Clipboard03.gif

Does Creating subtotals from the Calc Guide do it?

Clipboard01.gif
Attachments
sample_jh_DO NOT USE.ods
(19.36 KiB) Downloaded 11 times
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7825
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: SUM until next blank cell

Postby John_Ha » Thu Jan 02, 2020 4:12 pm

I couldn't get Subtotals to work but I am sure it should be what is wanted.

This does what you want but relies on what is in the blank squares.

Code: Select all   Expand viewCollapse view
=IF(N8=" ";N7;" ")

Clipboard01.gif
Attachments
sample_jh_V2_DO NOT USE.ods
(19.88 KiB) Downloaded 16 times
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7825
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: SUM until next blank cell

Postby Villeroy » Thu Jan 02, 2020 4:17 pm

Subtotals require explicit categories just like the pivot table and any simple solution with formulas.
Attachments
aoo100598specialSubtotals_Subtotal.ods
(20.09 KiB) Downloaded 16 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SUM until next blank cell

Postby Ssronica » Thu Jan 02, 2020 4:21 pm

John_Ha,

That's such a low-tech solution! It's awesome. Many thanks.

How do I mark this thing as solved?
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUM until next blank cell

Postby John_Ha » Thu Jan 02, 2020 4:24 pm

It is as nasty as it is low tech. Absolutely anything Villeroy suggests will be far better.

 Edit: I note Villertoy's solution uses Subtotal and I strongly recommend you use it and delete my nasty method Subtotal does not rely on blank cells and is self contained and foolproof.

Please trust me - my method will cause you problems in the future. 

Now you have the two IF expressions you should be able to combine them in column O and do away withy the helper column N.

See also Documentation/How Tos/Conditional Counting and Summation

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7825
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: SUM until next blank cell

Postby Ssronica » Thu Jan 02, 2020 4:48 pm

John_Ha,

I noted Villeroy's solution, but it seems to require manually inputting the SUBTOTAL function after every purchase (where the empty rows are). I like my spreadsheets to be fully automated.

But if I read his solution wrong, I'm happy to be corrected. I'll leave this unsolved overnight and come back to it in the morning to check any further replies.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm

Re: SUM until next blank cell

Postby John_Ha » Thu Jan 02, 2020 6:48 pm

I am out of my depth but you are not the first person with this requirement. Search with sub total for similar solved posts.

See Chapter 8 - Using the Data PilotChapter 8 - Using the Data Pilot (also known as Pivot Tables) in the Calc Guide - I think it is the solution.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 7825
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: SUM until next blank cell

Postby Villeroy » Thu Jan 02, 2020 7:16 pm

Select list
menu:Data>Subtotals...
Group by "Category" (the field I have added)
Aggregate "Base" with function SUM.
----------------------
I would never ever use this functionality because it "destroys" the source data by inserting intermediate results. This layout does not allow sorting anymore and "special rows" are a bad thing in any case just like your blank rows.
The pivot table does the same thing in a more convenient and more powerful way. A pivot table dumps calculation results into a separate table, preferably on a separate sheet. The source data remain untouchted. They can be sorted, filtered and calculated any way you want. Separation of raw data and results is a good thing. Right-click>Refresh updates a pivot table after the source table has changed. In order to add new rows to your source data, you have to insert new rows before inserting/pasting new data. You can also use paste-special with option "insert rows".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28641
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] SUM until next blank cell

Postby Ssronica » Fri Jan 03, 2020 3:45 pm

Many thanks to everyone for their help on this topic. Marked as solved.
OpenOffice 3.4.1;Win7
Ssronica
 
Posts: 33
Joined: Wed Sep 25, 2013 11:41 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 18 guests