collapsing/adding rows together into smaller tables

[sosososo]

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

table

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

[mgl]

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:

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 : 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

[LeroyG]

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:

1. 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.