Help

How to sum field values across rows

Topic Labels: Formulas
Solved
Jump to Solution
7695 2
cancel
Showing results for 
Search instead for 
Did you mean: 
John_Wang1
4 - Data Explorer
4 - Data Explorer

Is it possible to create a formula that sums values in other rows based on a filter with the current row’s other column value?

For example, let’s say I have 3 columns:

  • Topic (Primary Field)
  • Owner: Person object (other table) or email address
  • Score: Integer field

I want to calculate the sum of all Score fields where the Owner is the same.

From a table perspective, I was thinking about adding another column with a function like the following:

SELECT SUM(Score) WHERE Owner = this.Owner

This will result in the same value for all rows where Owner is the same, but I’m okay with that.

What’s the best way to accomplish something like this?

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

Welcome to the community, @John_Wang1!

Unfortunately, you can’t create a formula like that in Airtable, because Airtable formulas don’t understand the concept of other rows. Airtable formulas can only see their own row.

However, what you can do is use the group function to group your records by owner, and then choose the SUM function on the summary bar.

Sadly, we can’t use those summary bar numbers in any formulas, either. They are purely cosmetic.

(If you know JavaScript, my guess is that it is possible to write a JavaScript using the scripting block that can extract the sum for you, too.)

See Solution in Thread

2 Replies 2
ScottWorld
18 - Pluto
18 - Pluto

Welcome to the community, @John_Wang1!

Unfortunately, you can’t create a formula like that in Airtable, because Airtable formulas don’t understand the concept of other rows. Airtable formulas can only see their own row.

However, what you can do is use the group function to group your records by owner, and then choose the SUM function on the summary bar.

Sadly, we can’t use those summary bar numbers in any formulas, either. They are purely cosmetic.

(If you know JavaScript, my guess is that it is possible to write a JavaScript using the scripting block that can extract the sum for you, too.)

Great answer! The Group function is great for my purposes now.

I’ll explore JS blocks as well.

I’m just starting to use AirTable, but I like it a lot already.