Sum of all amounts in one column presented in another table


#1

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

  1. $20
  2. $20
  3. $20

etc

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


#2

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. :smiley:


#3

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)


#4

Thank you for your help!


#5

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.

image


#6

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.


#7

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.


#8

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.


#9

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?


#10

One is not able to select that many records manually!


#11

did you ever figure out how to do that?


#12

To link 300 (or 3000, or 30,000) records in one table to a single record in another table takes 2 mouse clicks and 2 keystrokes… (Well, after you link the first record, that is.)

  1. Create a view filtered to show just those records you wish to link. (Obviously, if you want to link all records in the current table, you simply use an unfiltered view.)
  2. Manually link the first record in the table to the target record. If the target record doesn’t exist, select ‘Create a new record.’ Note: While Airtable will let you link to unnamed records, in this case you should give the record a name — that is, make sure the primary field of the first record contains a value.
  3. Close the expanded target record by he ‘X’ in the upper right corner of the window.

Now here’s where those clicks and keystrokes come in:

  1. Mouseclick 1: Select the linked record field for the first record in your large table.
  2. Keystroke 1. Press Ctrl-C to copy.the value.
  3. Mouseclick 2. Select the column header — that is, the field name — for the linked-record field. A message will appear in the lower left corner of the screen reading ‘300 [or 3,000, or 30,000] cells selected.’
  4. Keystroke 2. Press Ctrl-V to paste the value. Depending on how many cells were selected, you may be prompted to confirm this is something you really want to do. Answer ‘Yes’. A message will briefly appear, again in the lower left, saying ‘Pasting…’. Once the value has been pasted into all selected cells, the message changes to ‘Pasting complete’, with an option to undo.

Since you pasted the same record name into the linked-record field of every record in that view, you’ve now linked each of those records to the single target record with that name. You are now free to follow @Jeremy_Oglesby’s advice, above.

There are other tricks and shortcuts possible in support of this all-to-one linking, as documented here.


#13

Hi @W_Vann_Hall

This is so neat.

I will be referring back to the above instructions.

I do enjoy learning new things and this is one of them.

Thank you,
Mary


#14

Awesome, thank you Jeremy_Oglesby !!!