How can I total my spreadsheet 'categories' on a second page?

[LibK]

Hello, please excuse me for my lack of knowledge - I am so new to using a spreadsheet and am stumbling around a bit!

I have created a spreadsheet for my business outgoings. I have cells A through to G with the following titles:
DATE / SUPPLIER / DESCRIPTION / CATEGORY / AMOUNT / GST / AMOUNT EX GST.
These columns are filled with my business info, row D (category) uses 12 different category words to describe the expenses (eg: rent, postage, printing, IT & computer, telephone etc.)

What I want to do is create a new page or chart or something which will take all the categories in row D, and the amounts in row E,F,G, and total all of the amounts of those categories which have the same name.

For instance:

• Postage - $5.00 - $0.45 - $4.55
• Postage - $5.00 - $0.45 - $4.55
• Postage - $5.00 - $0.45 - $4.55
• Bank fee - $10.00 - $0.91 - $9.09
• Bank fee - $10.00 - $0.91 - $9.09

would appear on a new page / chart as:

• Postage - $15 - $1.35 - $13:65
• Bank fee - $20 - $1.82 - $18.18

Any step by step help on how I can achieve this would be very appreciated.
Thank you in advance!

[mariosv]

Use a Pivot table, Menu/Insert/Pivot table

Pivot table

These can be a little tricky. Follow these steps:

• Click in your D1, hold down Shift, and then click in the bottom-right cell (bottom row of column G?) to select the area to include in your pivot table.
• From the menubar, click on Insert > Pivot Table....
• Click OK in the popup to accept your defined area as “Current Selection”.
• The next bit is not intuitive. You will get a “Pivot Table Layout” dialog, something like this:

• You need to drag the “Available Fields” to the correct box. So drag “Category” to “Row Fields”, and the rest of them (“Amount”, “GST”, “Amount Ex GST”) to the “Data Fields” box, like this:

• Now click OK, and a new sheet will open with your Categories summed.

You will need to repeat that when extending your data, but it is quick and painless once you’ve done it for the first time!

[LibK]

Wow, Thank you so much! I’m going to give this a go today. Much appreciated.

[LibK]

Thank you! Worked perfectly.

[dajare]

What you want is SUMIF – which, when referring to another sheet, could look like this:

=SUMIF(Sheet1.A2:A99,"Bank fee",Sheet1.B2:B99)

Which means:

• SUMIF = give me a sum when a condition is met
• Sheet1.A2:A99,"Bank fee", = the condition: when “Bank fee” is found in the cells A2 to A99,
• Sheet1.B2:B99 = then total the figures in the cells B2 to B99.
• where Sheet1. specifies a worksheet that is not this one.

There may be a way of summing a column when you don’t know (or don’t want to specify) a precise range – someone else may have a tweak there.

I’ve put together a little test ODS spreadsheet with this implemented, and attached it to this post.

FILE: libo-test-spreadsheet-summing.ods

[LibK]

Thank you David!. I had looked at SUMIF but was confused. The way you have explained it makes much more sense.

[dajare]

Glad these helped, @Libk - If they are useful, click the ^ arrow at the top left of the answer, and if one of them solves your issue, then also click the ✓ symbol beside that answer. (These are helpful signals for other users that look at this Q&A.) Let us know how you get on!