Formulas for adding, subtracting, division on column totals to show in a interface 'Number' element

[Josh_Southern]

Evening all,

As part of the warehouse inventory base i’m building, i’m also looking at the spend, profit and ROI of each record, with the intentions of displaying these on an interface for users to see.

Above is how i’ve got my current table set up. The Spend and Profit values are added in manually, and then a formula (profit / spend) is used for the ROI field. This works perfectly for each individual record, however i came across an issue when looking at the totals.

In my interface, i’m showing the total spend, and total profits using the Number element as a field summary. Unfortunately with ROI i cannot simply add up the percentages as this wouldn’t work. I need to be able to do Total Profit / Total Spend in order to get the correct value. Unfortunately i’m struggling to find a way to do this, as i cannot apply formulas to a Number element in the interface design.
Any help on this would be much appreciated.

[ScottWorld]

The only way that you can natively perform math across many rows (such as summing up all those rows in one column and dividing them by the sum of the rows in another column) is to link all of those records to the same record in another table.

So, for example, you could create a second table called “Months”. Then, back in your first table, change that month column from a text field into a “linked record” field and link it to your new “Months” table. Now, if you go back into your new Months table, you will see that you have a brand new record called “August”, and all 4 records from the first table are linked to it. So now, in your second table, you can create a Rollup field that pulls from one of the columns in your first table with the formula SUM(values). This will give you the sum of that column for the linked rows.

In practice, you would probably want to turn the month field into a “month & year” field, so it doesn’t get messed up next year with records showing up in August again.

p.s. This is the easiest way to do it, and it is also the only way to do it natively with Airtable. Alternatively, and this would be more difficult, but you could either (a) write your own Javascript script to handle this, or (b) you could use an automation tool like Make which can perform math across multiple rows.