How to column sum in Airtable like excel?

Question

I am designing airtable.
I met some issue.
I need to column total sum like excel.
For example:

Column1   Sum(Column1)
1         1
2         3
4         7
6         13

Like this. What is solution? Thank you.

Answer 1

Airtable is not a spreadsheet but a relational database, the behavior you're looking for isn't straightforward to reproduce because it's not meant to work like rows matter and aren't anything but a temporary sort - they don't, so they're not.

I know you were probably hoping for a better answer than another question, but I simply have to ask - why do you need to do this inside Airtable? Why not just use one of dozens readily available and entirely free spreadsheet/table solutions?

Generally speaking and especially if you don't need to manipulate that data afterward (a big if), Airtable already does the calculation you want on the fly and has it stored as part of its metadata.

The row-by-row addition would be doable using a combination of one autonumber field, a linked record (to another table actually polling for values) and a rollup returning data. I've done this back in the day once or twiCe and it was always an overengineered mess, even if I only had to deal with small integers like from your example.

There's Google Drive sync beta ongoing at Airtable right now. Just get that and do the calculations you need elsewhere?

The alternative is the Scripting app, but that might prove crippling in terms of how it could affect your automations quota. And recalculating fields by "hand" is... not sophisticated, to put it mildly.

But hey, don't take my word for it; Curiosity and nostalgia got the better of me so I gave this futile effort another go on your behalf, here's my best take at such an overengineered mess of a field-wide sum function that's wildly annoying to use but at least doesn't take all day to update records, even if presented with hundreds or thousands of inputs.

So, yeah... right tool for the job and all that: this ain't it, chief, but be my guest. You can clone the base from the Universe and everything will be ready for testing, just keep creating new fields or deleting the exiting few ones, then hit the "Run Script" button of the only app hooked into the base to see it recalculate the sum.

Dumping the code here as we, if anyone wants to set up a new testing environment manually:

    let table = base.getTable('Table 1');
let query = await table.selectRecordsAsync()
let cellsToAdd = [];
let sum = 0;

const keepSumming = x => sum += x ;

query.records.forEach( x => cellsToAdd.push(
    {
        "id": x.id, 
            fields:{
                "Column1":x.getCellValue('Column1'),
                "Sum":keepSumming(x.getCellValue('Column1')) 
            }
       }
        ));

//Airtable limits us to 50 table mutations per request, hence the splicing
while(cellsToAdd.length>0){
    await table.updateRecordsAsync(
        cellsToAdd.splice(0,50)
        )
};