Creating a SUM of, based on two parameters

[leowoods]

Hi there,

I have a question that I can’t seem to wrap my head around, so I would love to have your input. Let’s take a look at the following screenshot:

As you can see, I group records based on a name. In this case, we have “K” and we have “T”.

I would love to create a sum of the points for each person. So K would have 1 point, where T would have 5 points. There is indeed already a SUM, but I’m not able to sort on the SUM of each group (if I can, please let me know how, meaning that T would be on top of K).

So, I would like to create a field that is able to calculate the SUM from all the records for each name separately. I would not mind if I would see that same result in each record, but the idea is to create a scoreboard, where the highest sum of points for each person is on top (currently, when I sort, it just sorts on the individual point).

Is this possible, and if so, how would I go about this?

(Basically, I want to do some kind of count if on each name separately and on each month separately, although the month parameter I can probably tackle with a view)

Thanks so much!

[kuovonne]

Welcome to the Airtable community!

The sums that you are currently seeing are in the summary bar. Values in the summary bar are not accessible anywhere else. You cannot use them in formulas and you cannot group based on them.

One option is to move your {Name} field (and possibly other fields with repeated information) to a new table and connect the tables with linked record fields. You can then use a rollup field to produce your sum in the linked table. You can then pass that sum back to this table using another rollup field, and then group on the sum. The process is actually less complicated than it sounds.

[leowoods]

Hi @kuovonne, thanks for looking into it!

I noticed that this solution was offered a couple of times, but I have no idea how to do that. I can’t seem to understand what needs to be linked, how and how that rollup works. I have reviewed the documentation and tried for a while now, but I believe I would just need to see an example base with a couple of records (and thus two tables in that base?) to understand how it works.

I’m definitely not expecting this, but if you would have some time to create a dummy base with a couple of records to show this, where I would be able to examine the fields and their setup, that would be amazing. Thanks in advance!

[Kamille_Parks · Accepted solution]

Try doing the following:

1. Duplicate your {Name} field. (This is so you can easily undo the following steps if you don’t like the result. You won’t actually need two fields for name when done.)
2. Right click {Name copy} and click “customize field type”. Change the field type from “single select” to “Link to another record” and select “+ Create a new table”.
3. In this new table add a new Rollup field that targets your original table and whatever field you want the sum of. Use the aggregation SUM(values)

You will now have a table that automatically adds up each person’s whatever-this-field-is. For new records you can link to the [Name copy] table by simply clicking the plus symbol in the appropriate cell and selecting an existing name from that table or adding a new one.