Re: How do I automatically add up all values in a column?

8014 0
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

Let’s say I have the price of several items in column A, and I plan to add more items periodically. How do I create a field that automatically tallies the total of column A as I add each item?

Thanks for your help!

8 Replies 8
4 - Data Explorer
4 - Data Explorer

If you add a Currency Field within your table with your price within it.

Airtable will show a SUM at the bottom of the page calculating the total amount of all records displayed.

If a filter where added it would show total amount of filtered records,

Grouped groups would show total amount of each group and if filtered would show amount of group filtered.

Hope this helps.

Is it possible to then take the “total amount” and use that in other areas? For example in a Airtable function, or in a Zapier zap? I really like that Airtable rolls that date up, but I can’t seem to figure out how to access that data.

4 - Data Explorer
4 - Data Explorer

I have similar requirement. I need to take the column Total and display or use in a formula in a different linked table.

Is there a way to get the “total amount” and use that in other areas?

Not directly, in the way you mean. (That is, there is currently no way for a user to specify the column total for a particular view should be used as a variable in a formula.)

What you are trying to do is perform a multi-record calculation — which can be one of the most difficult things to accomplish with Airtable. Fortunately, though, yours is a pretty straight-forward application, which makes it possible, although not entirely painless.

This makes use of the technique of linking every record of one table to a single record of another. ISorry, @Jeremy_Oglesby…) Here’s how you would implement it:

  1. Create a new table in the base called [Calc].

    • Delete the default {Notes} and {Attachements} fields, leaving only {Name}.
    • Delete the three default records.
  2. In your main table (which I’ll refer to as [Main]), create a single-line text field called {Link2Main}.

  3. Enter ‘.’ — that is, the period character — in every cell in the {Link2Main} column.

    Easiest way to do so:

    • Select the {Link2Main} field in row 1 of your table and type ‘.’ (a period).
    • While that cell is still active, press Ctrl-C to copy the value.
    • Click the header for {Link2Main}. this will select all cells in the column.
    • Press Ctrl-V. This pastes the copied ‘.’ into every cell in the column.
  4. Right-click on the header for {Link2Main}.

    • Select ‘Customize field type’.
    • Change the field type from single-line text to linked-record, with [Calc] as the target table.

    Airtable whirs for a moment while it changes the field configuration. When it finishes, every record in [Main] will now be linked to the first (and only) record in [Calc], which is named ‘.’.

    You are now ready to define the fields that will let you sum all existing records in [Main]. However, for the values of any records added to the base later to be included in the total, you will need to link them to [Calc] as they are added, which you can do using the traditional linked-record mechanism, by dragging the fill handle of {Link2Calc} in an already linked record into your newly added records, or by copy-and-pasting a period character into the {Link2Calc} fields of your new records.

  5. In [Calc], create a rollup field called [Total].

    • It should follow the link back to [Main].
    • It should roll up whatever field in [Main] you wish to total.
    • It should use the aggregation function SUM(values).

Now, if you need to make use of the column total in a formula in [Main], you can do so by defining a lookup field in [Main] that looks to [Calc] and references {Total}.

To make use of the column total in a table other than [Main], you’ll first need to define a linked-record field in [OtherTable] that links to [Calc] and link your record(s) to the record named ‘.’. You can then access {Calc::Total} — that is, the {Total} field from the [Calc] table — with a lookup or rollup field.


Tweeeeenty characters

I just achieved this in a much simpler way.

I have a column that lists prices in one table, and I wanted to use the sum of those items in another table. In the second table (where I wanted the sum) I first added a lookup field that would pull from the price field in the first table. It lists all of the numbers in the second table in one field, which isn’t what we want quite yet but it gets all of the numbers in one place in your target table.

Then you just add another field in your target table that has a formula to sum that field up, and it will spit out one nice number, which you can then use in other formulas in that table as needed.

long story, airtable should fix it

Please! just having better export functions for tables would be so nice.