Formula for column A sum / Column B sum

[louis_hernandez]

I’m trying to write a formula that will take the total sum from ‘Participant Growth’ column and divide it by the sum from the ‘Output 1’ column. The sum in Column A is 3 and the sum in column B is 4. I’m trying to write a formula for this…The formula I currently have is: SUM({Participant Growth})/SUM({Output 1}). But it is not returning 0.75. Am I thinking about this correctly or might I need to create a new view?

[kuovonne]

Welcome to the Airtable community!

Airtable cannot handle column data the way a spreadsheet does. The sum of a column in the summary bar cannot be used by a formula field. Formula fields can only perform calculations with values in the same row/record and in linked records. Formula fields cannot perform calculations down columns.

A workaround is to create a new table and link all of the existing records to a single record in the new table. The new table would also need rollup fields to calculate the sums of the ‘Participant Growth’ column and the ‘Output 1’ column in all the linked records. Finally you would need a formula field to divide the sums.

[ScottWorld]

@louis_hernandez

Alternatively: I’m not sure if the math would work out the same, but you could create a formula field that is:

{Participant Growth} / {Output 1}

That would give you a number for each row.

Then, you could use the summary bar to show you the SUM or AVERAGE of that number. This SUM or AVERAGE could be calculated for either all your records, each group of records (if you group them), or your selected records.

There are also other summary bar functions to choose from as well.

Again, I’m not sure if the math would work out the same, but sadly & unfortunately, the summary bar is the only option we have for calculating summaries in Airtable without linking records to each another.

[Jonathan]

kuovonne:

Airtable cannot handle column data the way a spreadsheet does.

Why not? It’s marketed as:

Airtable works like a spreadsheet but gives you the power of a database

But it doesn’t seem to do either very well?

In a real spreadsheet it is easy to sum all values in a column using =SUM(A:A).

In a real database it’s easy to do it using a query like SELECT SUM(score) as sum_score FROM game;

Airtable can’t do either of these? You have to manually add all records to another sheet and then use a rollup to calculate the sum? And new records added using a form aren’t automatically included in the sum? I’m surprised and confused that there’s no way to do this.

[ScottWorld]

@Jonathan

Airtable can’t do either of these?

As I previously mentioned above, you can use the summary bar to get summaries within a single table. (Although you can’t use those summary values in formula fields, which would be nice.)

It’s marketed as:
Airtable works like a spreadsheet but gives you the power of a database

It’s 50% spreadsheet and 50% database. It’s not 100% spreadsheet and 100% database.

[kuovonne]

Jonathan:

Why not? It’s marketed as:

I’m afraid that is a marketing question that only Airtable can answer.

As community members, we can help explain what Airtable can and cannot do, but we can’t really say why Airtable chooses to market itself the way it does.

Airtable can do many things that traditional spreadsheets and databases cannot do, and it cannot do other things that they can.

Jonathan:

Airtable can’t do either of these? You have to manually add all records to another sheet and then use a rollup to calculate the sum? And new records added using a form aren’t automatically included in the sum? I’m surprised and confused that there’s no way to do this.

Airtable can calculate sums in the summary bar, but it cannot include summary bar information in formula fields. New records added using a form are automatically included in summary bar calculations.

If you have a specific use case that you are trying to accomplish, feel free describe it and perhaps the community can help you.

Even if Airtable does not directly support what you are trying to calculate, it might be possible to calculate using custom code in Scripting block.

[Jonathan]

ScottWorld:

(Although you can’t use those summary values in formula fields, which would be nice.)

Yes that’s what I’m asking about.

ScottWorld:

It’s 50% spreadsheet and 50% database. It’s not 100% spreadsheet and 100% database.

:confused:

kuovonne:

If you have a specific use case that you are trying to accomplish, feel free describe it and perhaps the community can help you.

The same types of things as this thread: Calculations like =sum(A:A) - sum(B:B) in a spreadsheet (but with everything in the column included, even records added later).

[Jonathan]

Apparently there’s no other way to do this, since there’s even a help page describing this method: https://support.airtable.com/hc/en-us/articles/360042106174-Find-the-sum-of-an-entire-field

[ScottWorld]

Jonathan:

Apparently there’s no other way to do this, since there’s even a help page describing this method:

Those are the exact same 2 answers that @kuovonne and myself have been giving throughout this thread.

As @kuovonne mentioned, you could write your own JavaScript to summarize records. You could also use an automation tool like Make to summarize records as well.

If none of the above options work for you, then Airtable may not be the tool for you.

[kuovonne]

If you are on a free Airtable plan, as @ScottWorld mentioned, you can use a third party service, such as Zapier or Integromat to automatically create the links so that Airtable can perform the calculations in rollups and formula fields.

If you are on a pro Airtable plan, you can use the new Automations to create the links, or you can use a script to perform the calculations without links.

Here is a demo base with automations that calculates column totals when a new record is created, with an input form for creating new records. Because shared views of bases do not update automatically, you will have to manually refresh the the webpage with the base to see the updated calculations after you submit the form.

If you are interested in this type of solution but need to hire someone to implement the automation script, feel free to reach out.