Create a table showing subtotals from another table

[Mark_Jamieson]

Hi all,

I am having a little trouble. I have a base where all my transactions are summarised in a grid view as below.

That works like a dream. Now I need to do two calculation, First I need to minus the subtotal Cost of Goods from the Income. I know I cannot do that in this table, but I think I can create another table where I can do this, based on these records. I am just having trouble making it work. I have tried but for some reason the linking is not working correctly.

Can anyone help me with some clear instructions how to set up the second table so I can calculate my Gross Profit (once I have done this I will be able to work out the rest).

If these is an extension that anyone knows that allows calculation on Subtotals that would be good. I just do not want to have to export to excel each time I update!

Many thanks, Mark

[Pascal_Gallais-]

Hello Mark,

Formulas apply to columns no to rows.

It means that to achieve what you want to do, you need a table with the months in rows and the values in columns. You will then be able to add and substract columns as you want.

The methodology to get there depends on how your fact table is built. If you have a detailed table with dated sales and expenses, you can link each row to a "summary month table" to do what you want.

Regards

Pascal

[TheTimeSavingCo]

With just the screenshot to work with (let's call the table in the screenshot the "Data" table), I would try:
1. In the Data table, create a formula field that will sum up the values from all the months called "Totals"
2. Create a "Summary" table and link it to the "Data" table
3. Create a single record in the "Summary" table called "Summary"
4. Link all the "Income" and "Cost of Goods Sold" records to that one record in "Summary"
5. In the "Summary" table, create a rollup field called "Total Income" and use the value from the "Totals" field from step 1
  - Use the formula "sum(values)"
  - Use conditional logic to only use the values from records of type "Income"
6. In the "Summary" table, create a rollup field called "Total COGS" and use the value from the "Totals" field from step 1
  - Use the formula "sum(values)"
  - Use conditional logic to only use the values from records of type "Cost of Goods Sold"

You should now have two fields in the 'Summary' table that display the values you need and you can deduct them from each other

---
While the above should solve your problem, it's worth noting that your data doesn't look like it's set up optimally for Airtable.  Specifically, the months should be rows instead of columns so that you can easily add month-year data

I'm not familiar with how your data is set up or what your business logic is though and it's very likely I'm not understanding something critical that's leading you to build your table in this manner!

[dilipborad]

Hello @Mark_Jamieson ,
There are 2 options if you know about automation scripting.
If you need to go with linking tables and formulas then this best solution works perfectly as both of @Mark_Jamieson  & @TheTimeSavingCo suggest.

But you don't need that much complexity in the structure and you still need to calculate those things and update it on specific fields then use Automation with scripts.
The important thing is if the data(number of rows) is large then use an extension script. But extension script always needs manual input(run command) to execute it while automation script is run automatically based on schedule or trigger point.
How to Run a Script Action | Airtable Support.
Scripting Extension - Overview | Airtable Support