Dynamic table - How to insert data into a Table and have either a graph/table automatically but selectively update with the new references

As the title suggests I'm looking to keep track of my financial income and spendature and want to be able to log new data into these tables chronologically by date but still be able to summarise the spendature's or income sources by categories, vendors, detail etc. hopefully through the same table

Some examples of the research and ideas ive come up with so far but don't quite achieve what I'm looking for:

• this support thread which details ideally what I'd like to do but is still one tier too simple as it does not factor in my need to include costs as well as total costs (sums). also references this thread
• pivot tables - not available in numbers but excel, from my limited knowledge and research I believe this is also along the lines of what I'm after but wondering how to implement this into Numbers, as it's my preferred choice of spreadsheet
• Manually building sub-tables that I have to manually copy and paste from the master table based on their categories each time I log something new in and then reference my way into those summary representations.

I've attached a mock-up of what I'd ideally like to achieve. The cells above the tables are the summary cells in terms of 'category' or 'vendor' so I can see how much I'm spending on food succinctly each month for example.

If there are easy and direct methods to achieve what I want through other programs like excel or google sheets I'm open to it but my preference is achieving this in numbers due to compatibility with iOS devices