I have an Excel workbook that list all my expenses by month (one sheet by month) and by account (one table by account [bank account, credit card, cash]).
I want to summarise and group by Subcategory all the entries from the three accounts (tables [bank account, credit card, cash]) for each month, and also for the whole year. I need four kind of summaries - one for all entries (Bruno, Family, Cassandre), another for Bruno's Group, another for Family's Group, and another for Cassandre Group. I want only the Subcategories that have entries (don't display the Subcategory if there is no entry for this Subcategory)
Plus I want short table with the total for each Subcategory (only the Subcategories that have entries) and the total for all entries (for the concerned Group)
I created something similar using Google Sheets, you can have a look there: [login to view URL] I also attached a screenshot. The only difference in this Google Sheets, it's that all entries come from the same table (there is not a table for each Account [UBS, Mastercard, Cash])
1-Summarise and group by Subcategory all the entries (for Group Bruno, Group Family, Group Cassandre)
2-Summarise the total for each subcategory (only the Subcategories that have entries) and the total for all entries (for the concerned Group)
3-Take the three tables for each month in consideration (UBS, Mastercard, Cash)
4-Be careful, in the file "Bruno's Expenses - 2020", there are some rows used only for autocomplete feature (rows > 100). These rows should not be listed here
5-All empty lines from "Bruno's Expenses - 2020" document should not be listed there. Only rows with relevant info filled (date, debits/credits, recipient, subcategory, category, group) should be listed there.
6-Must be compatible with Excel for Mac
7-Must use index() and match() functions (avoid Pivot Table)
8-Must be dynamic, accept new entries or modification of existent entries and be updated in live
9-Summary tables should be editable, and should retain changes when an entry is added, deleted or modified.
10-The summarise must be refreshed automatically when an entry is added, removed or edited.
45 freelancere byder i gennemsnit $106 timen for dette job
We are professionally Qualified Accountant with excellent spreadsheet skills and we adhere to time-line with accuracy. we have the right skills and knowledge to complete this task. regards Ashish