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


#1

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!


#2

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.


#3

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

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?


#5

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.


#6

:wink:

Tweeeeenty characters