Ask Your Question
0

collapsing/adding rows together into smaller tables

asked 2020-11-09 19:04:14 +0100

sosososo gravatar image

updated 2020-11-09 19:26:35 +0100

Opaque gravatar image

let's say a portion of my data looks like the top table in this picture

table

image description

I've manually made the other two tables, in which the first and second column have been "collapsed" to just give me the sums for the variable in B and C and the variable in A and C. I have some large spreadsheets, and obviously it's not practical to do it by hand like I have done above. Is there a way to automatically do this?

Also, I am very new, your patience with me is greatly appreciated.

[Edit - Opaque] Inserted picture directly to details of question

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2020-11-09 22:15:06 +0100

mgl gravatar image

Hello @sosososo

Use the function SUMIF() to easily find these sub-totals. Some other ways exist but this one is easy. Let me show you:

SUMIF_example

Apply SUMIF to a test range, A3:A8, to be compared with a criterion C12to start with, and if it works, add the amount on the third range,C3:C8 for the same line.

Note $ in the C12=SUMIF($A$3:$A$8;A12;$C$3:$C$8). They make unchanged the definitions of the test range and of the sum range, if this function is copy-pasted in another cell, like C13 and C14. Only the criterion will change from A12 to A13 and A14 respectively.

Here is the file to help you with : C:\fakepath\275801-collapsingadding-rows-together-into-smaller-tables.ods


To show the community that the question has found its answer, please click on the ✓ aside the top of the correct answer and vote by clicking the ^ caret of all usefull answer.

Please do not use Add Answer but edit your original question to enhance the details of your question (answers are reserved for solutions to a problem on this Q&A site). Thanks in advance …

Kind regards, Michel

edit flag offensive delete link more
0

answered 2020-11-09 22:57:38 +0100

LeroyG gravatar image

And to collapse the "day" and "purchase" labels:

  1. Select data in column A
  2. Choose menu Data - More Filters - Standard Filters…
  3. In the Value field choose Not Empty
  4. Click on Options and check:
    • No duplications
    • Copy results to:
  5. Click in the cell in which do you need the filtered data to be pasted (if the dialog is over the destination cell, you can move it by dragging it from its title bar)

Repeat from step 1. for column B.

Tested with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.


Add Answer is reserved for solutions.

Press edit below your question if you want to add more information; also can comment an answer.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-11-09 19:04:14 +0100

Seen: 44 times

Last updated: Nov 09 '20