Skip to main content

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?

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.)

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.

Reply


Terms & Conditions