Numbers Pie Chart Formula using multiple tables and varying categories

It's a little messy, but it works. And it can be adapted to whatever other categories you need. T means table, and it assumes that you have the category in Column B of each table and the amount in Column C. You can change this if needed. In a new table, use two columns, one with the category and one with the formula.

=SUMIF(T1::B,"=Groceries",T1::C)+SUMIF(T2::B,"=Groceries",T2::C)+SUMIF(T3::B,"=Groceries",T3::C)+SUMIF(T4::B,"=Groceries",T4::C)+SUMIF(T5::B,"=Groceries",T5::C)

Then you can create a pie chart using this new table as the data references.

This is pretty easy. Here's how I did it using an intermediate table:

I started with 5 separate tables of expenses. the categories in each are the same, but they don't have to be.

Then I created a separate table to summarize the data.

For Summary Table::A2 I set this as a Pop-up Menu with entries that match the categories in T1..T5. This makes it easy to pick which category I want to chart.

For cell Summary Table::B2, I set the formula:

=XLOOKUP($A$2,T1::A,T1::B,0,0,1)

This performs a lookup taking the value of cell $A$2 (the pop-up menu) and searching for it in column A of Table T1 (T1::A). For any match, it returns the corresponding value from T1::B. Missing matches return 0.

Copy this formula across the row, changing it to refer to T2, T3, T4 and T5.

Now, picking a category from the menu automatically updates to show the corresponding values from the individual tables.

It's also now a cinch to create a pie chart - just select the Summary Table and click the Chart icon in the toolbar. Pick a pie chart and you're done. Those numbers are charted, and selecting a different option from the poup menu in A2 automatically updates the chart

If you find you’re wanting to do charts based on multiple tables then that’s a strong sign that you would be better off keeping the data together in one table to begin with rather than scattering it in various tables.

That makes charting and summarizing MUCH easier.

And you lose nothing. You can easily use SUMIFS and Categories and Filters and Pivot Tables view subsets like what you have currently in your separate tables.

SG