Investment per period - how to get the sums of different columns to different rows?

[JanBolmeson]

Hi!

I am totally stuck here, so maybe I can ask you guys where I went wrong. I have the following situation as in this demo base:

Airtable

Demo220708 - Airtable

Explore the "Demo220708" base on Airtable.

In the first table I have different investments as rows with their total values for different periods as columns:

Everything is great and I can see the total sum of all investments in the bottom row.

Now my question - how do I get the total of each column into the period summary? I.e.

The problem is that I cannot use rollup - because that is linked to a specific column, hence I get the same sum for all periods.

Thanks in advance… :pray:

[ScottWorld · Accepted solution]

Unfortunately, those summary bar numbers are not usable anywhere within Airtable. They are only available visually onscreen for cosmetic purposes only. You may want to email support@airtable.com to ask them to please make those numbers usable elsewhere in the product.

Your only 3 options are:

1. link records & use rollup fields
2. write custom JavaScripts, or
3. use an external low-code automation tool like Make.com.

[augmented]

Hi Jan. I think your question illustrates one of the most common challenges people (with a spreadsheet background) have, when getting used to Airtable. It helps me to remember that, in Airtable, going “long” vs. “wide” in table design, gives more flexibility.

In your case, I would make the Investments table have 3 basic fields:

1. Name - short text
2. Quarter Ending - link field to your “Period summary”
3. Value - Currency

I would simplify your “Period summary” table to remove the “Period” column (single select) and take the word “Period” out of your primary field.

Now, in “Period summary” you will have one row per year/qtr, the linked field to “Investments” (holding multiple values), and a rollup field for the total value that you want.

Make sense?

[JanBolmeson]

Thanks @augmented. It does make sense, thank you. I will see if I can implement it. :thumbs_up: