I have a Numbers spreadsheet that I am porting to Airtable and am struggling to replicate a summary table where I display sums from columns of other tables when they match the values in the summary table. For instance, the summary has rows with names like shoes, shirts, hats, etc. The columns are total items, price, etc. In Numbers, I just use a lookup function to sum the values (price, item total, etc.) from my inventory table for each product type. I’m struggling to figure out how to replicate this with Airtable’s approach.
Since I can only run formulas with data inside the current table, it looks like I have to create separate columns in the inventory table to sum the item attributes and then use a lookup to pull those values into the summary. If I’m right, that’s a nasty, inefficient way to do this (I think I need to add 16 unnecessary columns to my inventory and then pull all of them into my summary table)… In Numbers/Excel, I could nest a VLOOKUP so that I can pull from any table without pre-processing it within its table.
Is there a better way? I feel like I must be missing something…
Actually, the approach above isn’t working. I can sum all of the values within the inventory table (requiring 12 “unnecessary” columns), so I technically have the totals I’m looking for, but I don’t know how to get them into the summary table.
Here’s what I want in my summary table:
Rows: Hats, Shirts, Shoes, etc.
Columns: Total Items, Total Cost, etc.
Where I’m stuck is how do I create a formula/lookup/rollup that uses the row name to grab data from the inventory table?