Pivot table with SUMIF values

[benjhardie]

Hello!

I need some help with a pivot table and would really appreciate any solutions or ideas to solve my problem!

I want to make a pivot table from my transaction data to summarise my finances. I have made the attached table that uses a data connection to my transactions to get the data.

I want my pivot table to show me my total spend for each category and when I expand each category I want to see my spending grouped by transaction name (as in the example image attached). I also want to be able to filter this table by a date range. This is all pretty straightforward if each transaction only had 1 category, however my transactions can be split across multiple categories (Columns F - K) so I essentially need to incorporate SUMIFS into the table somehow (I think!).

Does anyone have any ideas as to how I can do this?

Thank you!

[oeldere]

use 1 column for all expencies instead of serval columns.

you can use Vlookup to group the data.

after that you are able to make a pivot table to anlyse the data.

[benjhardie]

Thank you for your reply!

I’m not quite sure what you mean in your suggestion. These transactions may have up to 3 categories for the same transaction (e.g a transaction at Sainsbury’s could have £5 in category 1, £2 in category 2 and £10 in category 3 for a total of £17) which is why we have the separate columns rather than 1 column which I think you are suggesting?

Thank you 😊

[oeldere]

I have no mac so I don't know it is works for a mac.

But if you upload an small excel file, I will show you.

[benjhardie]

I've attached some sample data.

Thank you so much!

[oeldere]

See an example of a pivot table (of your data).

[benjhardie]

Thank you for this. Unfortunately, this doesn't give me the results I'd expect though.

As you can see from my highlighted table, the sum of the entertainment category is -£84.50 whereas the pivot table you have created gives a total of -£59.00.

Attachment 687578 Attachment 687579

[oeldere]

If you work with a pivot table, you need to store the data rowwise instead of in colums. I changed the blue cells in the original data.

After that I refreshed the pivot table (entertainment category is -£84.50).

See the attached file.

[oeldere]

I am not able to attache a file at the moment. Will post it later.

[AliGW]

Why are you unable to attach the file?

[oeldere]

@AliGW

I guess it has the same reason.

I will try if I can add it now.

Earlier I got, after I wrote the text, and add the attachment, I got a white screen and was not able to post it (the text was gone).

Edit
I am also able to edit this text.

[benjhardie]

I'm struggling to understand. The values in the table still don't match what I'd expect.

The entertainment category (in yellow) should now give me a total of -£109.50 not -£84.50 and the gifts category (in green) should produce a total of -£20.00 not -£10.00.

This table only uses the values in the first category column but I need a way to Sum the data from all 3 category columns.

Thank you!

[oeldere]

you have to look in sheet 1 (PT).

Entertainment -84,50

Gifts -10,00

[benjhardie]

I am looking at the pivot table you created in Sheet 1 (PT). These totals aren't the totals I am looking for.

Your pivot table is taking is taking the orange values, for example, and I need to get a sum of the values in green.

Each row is 1 transaction (e.g. Sainsbury's). The 'Total Amount' column at the end is my total spend (e.g. £20) but the 3 category columns and their amounts show me how that £20 was spent across different categories (maybe £10 on groceries but £10 in the gifts category because I bought something for a friend and that isn't part of my groceries spend). I need my pivot table (or similar) to show me Groceries = £10 and Gifts = £10.

Does that make sense?

[oeldere]

You can add more analyse objects to the pivot table.

Then add the date in the pivot table.

See the attached file.

[benjhardie]

This doesn't solve my problem The values in your pivot table in your attached document are still incorrect.

[oeldere]

Please Login or Register  to view this content.

Maybe it is since you are on a MacVersion.

The result looks according to your request (to my opinion).

[benjhardie]

I have looked again at your pivot table on a Windows PC and it's the same. In the file you sent me, can you see why the entertainment category should be -£109.50 and not -£84.50 as shown? Your pivot table completely ignores 4 columns from my data. I need to be able to include all of it.

[CK76]

I'd recommend doing following, before you do anything else.

Flatten you data table from cross-tab structure.
I.E. Columns: Date, Transaction Name, Category,Amount, Receipt

Normally, I'd do this transformation via Get & Transform. But since that's not available in Mac version...

You can do this through VBA, formula, or manual copy/paste.

Did a quick sample using manual method. See attached (I ignored Receipt column as that was blank for all rows).

Sheet3 has the pivot (filtered out (blank) in Category column) and Sheet2 as restructured table.

[benjhardie]

Thanks CK76 for your input! The problem with this method is that my source data is a power query so the data will grow and change and if I add rows (so that all categories can be in one column) it will break my connection. If I create another power query from that original one on another sheet to appear like you have done, would that be dynamic and be able to add rows/change values without writing over itself?

[benjhardie]

I've managed to do what I was looking for by creating a new power query from the existing table that splits each transaction into 3 separate rows (for 3 different categories) and then filtering out the rows where the category is blank. Now all the categories and totals are in 1 column each, I can create my pivot table from this table and then hide the sheet.

Thank you CK76 for helping me solve this. Power Queries are very new to me so I wasn't aware you could split by row until you prompted me to look!