Remember you can essentially write a formula within the rollup aggregation function window; simply use the keyword 'values' to reference the rolled-up field. The one relatively constraining limitation is that you can’t address directly more than one rolled-up value, which presumably your suggested enhancement could.

Exactly - the ability to reference multiple Rollups within a formula would be nice, but also, I think that to make it compatible with the ARRAY_EACH() function being proposed, you’d have to be able to address the Rollup within the ARRAY_EACH() function itself - I’m not sure it would work if you just referenced a distinct Rollup field. Maybe I’m not thinking properly about that.

Also, I’ve always had trouble with writing formulas in Rollup fields - perhaps I haven’t been thinking about using 'values' in the right way

I’ve lost the ability to edit this post, sadly.
(explanation here)

Until (hopefully) I can edit it again, here’s another addition to the list:

Function

Formula Field Version

PROD(value 1, value 2, value 3, etc...)

Rollup Field Version

PROD(values)

• Takes any number of numerical values and multiplies them together to obtain the product of those values

Reference Link

Functions

POP(array)

• Returns the last value out of an array (the array most likely being a reference to a Rollup field)

SHIFT(array) .... or ... PULL(array)

• Returns the first value out of an array (the array most likely being a reference to a Rollup field)

Link

Function

CURRENCY(number, 'currencySymbol')

• Takes a number as either integer, decimal, or field reference, and a currency symbol ('$', '€', '£', etc…); returns the number rounded and formatted as currency

Link

CURRENCY(number, 'currencySymbol')

Adding live conversation rates to a CURRENCY function would be killer.

Wow. NEED this so badly.

:frowning:

Is there no way to automatically get the value of a linked records field automatically now? An API populates my table at the moment from orders on my online store and I need to look up shipping costs per shipping method on a linked table. Right now I have to manually copy the shipping method value to the linked record field to get the lookup to work. It’s a pain.

I haven’t yet played with the API, but using Integromat (which uses the API under the hood) to edit Airtable data, I can pass text into a linked record field and it will make the link if it finds a record with a matching name in the primary field. Not sure how much extra work it would take directly with the API to do something similar, but perhaps @Bill.French can offer some insight on this.

Check out Ragic.com… it is not as clean as airtable, but if you are looking for something that is still quick to set up, AND allows the implementation of more powerful formulas and forms … all for about the same price as airtable, then Ragic.com is what you need. Airtable is great for what it does… but dont try to use a hammer when you need a mallet. Choose the right tool for the right job. I’m tired of fighting airtable to get these features. Airtable is great…just going in another direction.

Hi everyone,
I am pretty new to Airtable so in the midst of generating a few bases. What I have noticed so far and can see that people have been requesting for it for a while is COUNTIF, COUNTIFS, SUMIF and SUMIFS.

Linked to the above formula and applicable to current SUM and COUNT is a function that I use in Power BI called ALL. ALL forumla basically take any filter out of the equation and is really useful for taking the grandtotal to calculate any ratio or present it on its ok.

For example, the sum and rollup can show a told and when we filter the information or group them, we can see the total for that group alone. However, the only place to see the grandtotal is at the bottom. Moreover, the grandtotal that appears at the bottom of the base cannot be refered to in any formula. Therefore, adding the ALL function will transform the capability of Airtable in terms of creating calculated fields.

Happy to provider further clarification if not already clear.

@Jawed_Neshat – it sounds like what you are wanting to do might be possible with conditional rollups.

In short - create a field in the table being rolled up that reproduces the value in question, (say, an amount), but only if some condition is met. Otherwise, the field is blank. Then in your summary table that is rolling up the records, do a SUM(values), AVG(values), etc. rollup on that conditional field.

In support of the lookup function, I think a huge value in this formula, would be to enable iterations. There are some extremely elaborate base structures just to overcome the fact that Airtable currently does not support any type of iterative formula.

A simple example: I have two tables

1. Projects Table
2. Tasks Table

I assign records in the Tasks table to a project, lets say 3-50 tasks per project. Then each task is linked to the next and previous task (a pain in itself currently, but lets not worry about that here). I simply want to sort by task number in the sequence. This sounds easy, but currently isn’t!

If I could have a formula field that simply looked up the same field on a linked record and added 1, I would be golden! Unfortunately, instead I had to create 50 lookup fields to achieve an illusion of this capability limited to a maximum of 50 records.

Of course a circular reference might occur if someone linked the record to itself on accident, but if that could just throw an “error” I would be very happy :slightly_smiling_face:

@Jeremy_Oglesby

I think one of the key missing features is the ability for us to use Airtable’s own internal summary functions within our own formulas!

Airtable cosmetically displays summary fields in both grouping headers and view footers, but we have absolutely no way to grab those values and use them in our own formulas.

Airtable only gives us access to summary functions in Rollup Fields and Count Fields, which require us to link to another table.

But we have no access to summary functions for the records in a single table.

In essence, Airtable’s summary functions have no concept of other records within the same table, and they have no concept of groupings within the same table.

Cosmetically speaking, Airtable displays the values that we need onscreen, but we can’t use those values in formulas.

We really need access to these onscreen summary functions in our formulas.

And, to be clear, there are 2 different types of summary functions here:

1. Groupings
2. Entire view

This would be a killer feature which would save us tons & tons of headaches, because currently we have absolutely no way of creating formulas that can summarize data within a single table. We are always required to link to other tables to get summary functions.

Agreed! I was just banging my head on this wall today. I want to summarize data in a table without having to link every record from that table to another table! That is not an option for a large set of records. The group summaries are great, but not accessible.

Yes. That’s a great way to sum it up:

“Group summaries should be accessible to us.”

What would be great is if functions defined in Scripting Blocks are allowed to be used in Formula fields.
That way, if there is a function that is needed but missing, we have the option to write a new function that we can use.

Found this thread when I was trying to find an array difference formula, where array 1 and array 2 are compared and then the difference is returned within array 3. Looks like I’ll need to automate the solution. :frowning:

Thinking about the Summary shortfall you’ve mentioned, this too has annoyed me over the years. It would be great to see either Summary functions added to the Formula Field, or perhaps, even a designated new type of Field called “Summary” that returns specific summary data (albeit, duplicated for the cells within each group, perhaps it could have an array [“Count”, “Value”] ).

thanks for sharing these ideas! super valuable :pray:

You can use this way for your goal. Topic is here, but I don’t think it has more helpful info to add to my quote and picture.

Unfortunately, it’s been 4 years now and none of this has been added. While it’s true that scripting automations (which were implemented after this feature request was made) can basically address all the same use-cases, that is much less approachable to many people than a formula field, and a lot less convenient in a lot of cases.

The power of Formula fields is also from API responses. Through my initial use of them, I noticed that data sent to Airtable via an API call then returned the calculated field in that same operation - Although I’m yet to leverage this, it’s one thing to keep in mind when deciding whether data needs to be calculated in a formula field or returned by a scripting Automation.