Sum of all amounts in one column presented in another table

Henry_Dalziel · ‎Apr 10, 2018

I have a column in the first table with dollar amounts like this:

$20
$20
$20

etc

I would like the second table (in the same view) to total these amounts. How would I go about doing that? Thanks

Jeremy_Oglesby · ‎Apr 10, 2018

Make a field of type “Link to Another Record” in the first table, linked to the second table.

Link each record in the first table to a record in the second table which will serve as the record to totaling Table 1.

In Table 2, make a field of type “Rollup” that looks at the Field that Links to Table 1, and at the field with the dollar amounts in it, and has an aggregation formula of “SUM(values)”; then click on the “Format” tab and select “Currency” as your format.

Here’s a simple base demonstrating it: https://airtable.com/shrvc72wv8VvZkQLi

Feel free to copy. :grinning_face_with_big_eyes:

Jeremy_Oglesby · ‎Apr 10, 2018

Although, I just noticed, on re-reading your question, that you said “(in the same view)” - and now I’m not sure I understand what you’re asking.

Airtable’s parlance can be a little confusing sometimes, which can make it difficult to understand what people are asking.

A base is the whole database you create.
A base can have many tables in it.
A table contains many records (rows), fields (columns), and views (differently organized, grouped, and sorted displays of the same records).

When you say you want a second table (in the same view) to sum up the dollar amounts in the first table, does my example above get at what you mean?
Or do you mean you want a second field in the same table to sum the dollar amounts?
If the latter, this is not possible with Airtable’s basic functionality because their “formula” fields act on other fields, not on cells (they have to because of how Airtable is set up).

Also if the latter is what you are after, as inelegant as it may seem, you may find a solution here: Multi-record calculations (c.f. 'running balance,' 'row number,' and the like)

Henry_Dalziel · ‎Apr 13, 2018

Thank you for your help!

Trey_Wentz · ‎Apr 27, 2018

So I am sitting here trying to figure out this exact problem myself and I appreciate someone from the community offering a sample solution, but it just seems a little clunky to have to create the link for every record every time I add something. Am I missing something here?

One reason this baffles me so much is the value I want on my summary sheet is actually seen on the first tables and updates automatically. Why can’t I get that value on another sheet/table?

It just seems odd that you can’t run a SUM formula to summarize data off another table.

Jeremy_Oglesby · ‎Apr 27, 2018

Nope, you’re not missing anything. Airtable is a relational database, not a spreadsheet. Rows do not have a spacial relationship to each other, like they do in a spreadsheet; therefore, formulas cannot reference specific rows - only fields in the same row.

I wouldn’t say it’s clunky - it’s just different, because this is a different kind of tool than a spreadsheet.

That’s exactly what you’re doing when you link all the records to another table and run a “Rollup” --> SUM(values) on it all.

Trey_Wentz · ‎Apr 27, 2018

I do understand that is is a relationship database. It seems to be missing a SELECT * FROM Option. I was hoping I did not have to manually link each record. It means I cannot automatically update my summary rollup without adding a relationship to the first table.

Jeremy_Oglesby · ‎Apr 27, 2018

Table query functions would be really handy!

I think Airtable has probably shied away from implementing more powerful functions like that for fear that it flies in the face of their user friendly mantra.

¯_(ツ)_/¯

I’d +1 a feature request for query functions.

Lety_Kemp · ‎Oct 08, 2018

I’ve been sitting here for half a day trying to get this right also. I have over 300 fields to link for a rollup, so there’s just no way I’ll manually link these…

Any other way I can go about using the SUM value in another table?

Lety_Kemp · ‎Oct 08, 2018

One is not able to select that many records manually!