Formulas

Hi. Let’s say that every record has a field named: Cost. The sum of the cost of all records appear at the bottom. Can I use that sum in a formula? For example if I want to calculate the percentage of every record’s cost ( when 100% is the sum ) Thank you Ilias

Hi, we do weekly reporting through Airtable - but we would like to group the reports by the meeting date (every Monday). Typically a record is submitted on Friday (can also be any other day), and we would like to set the ‘Meeting date’ automatically to the next Monday of the submission date. Now we have to add it manually. Is there a way to use the Submission date option to fill in the next Monday in the ‘Meeting date’ column?

Hello! I have a formula which builds record names based on the values of other columns: {Name} & " (with " & {Secondary Name} & ") " & " - " & {Event} & "\n " & {Year} & " | " & {Room} Result: John (with Jim) - Conference 2020 | Green Room However if there’s nothing in “Secondary Name” it displays: John (with ) - Conference 2020 | Green Room How do I get it to remove the "(with ) " when there’s nothing to fill it with? The secondary names are linked to other records so unfortunately I can’t just add the brackets to the column entry instead of putting them in the formula. Thanks in advance!

Hello, I am trying to create a formula that automatically assigns a Team based on Column 1 which contains company names. I am running into trouble, it seems, due to the length of my formulas. I am wondering if anyone can assist. Below is the formula I created in Word, it works in Airtable when I shorten it to cover only half the companies. I have tested with several different orders to make sure it isn’t just the next company in the series that is causing an error. I have about 20 companies I am assigning 1 of 2 teams to. SWITCH({Distributor Name}, “Company Name”,“Team 1”, “Company Name”,“Team 1”, “Company Name”,“Team 1”, “Company Name”,“Team 1”, “Company Name”,“Team 1”, … “Company Name”,“Team 2”) I have also tried IF formulas such as the below, however no matter how I’ve formatted them they automatically cut it short and only follow the first 2 expressions. IF({Distributor Name}=“Company Name”,“Team 1”), IF({Distributor Name}=“Company Name”,“Team 1”), IF({Distributor Name}=“Company Na

Help! I have two fields, one contains a date, the other contains a frequency. Based on this I want to have a third field populated with the date of the next occurrence. the first IF statement works and correctly sets the date, the second is ignored and no error. It just does not populate the field at all. I am using the following: IF(Recurrence = “Weekly”,DATEADD({Invoice Due Date},7,‘Days’, IF(Recurrence = “Monthly”,DATEADD({Invoice Due Date},1,‘Month’))))

hey, whats the formula to subtract a week from a field with a date? thanks alot!

Question: New to Airtable. Looking for a way to use an if then statement to assign Collaborators or employees. Example: If we are installing a specific type of account, autofill the “Assign to” field. New account: Showroom - auto assign to: Natasha. Not sure if this is possible or if there is a workaround. Thanks!

I’m not sure if this can be done but here’s what I’m looking for: I have a base with two basic tables: Jobs and List The Job Table has the following fields Job Name, Job Number, Custom Calculated Field (CCF) 1 Formula (CCF 1 Formula), CCF 2 Formula…CCF 6 Formula, and a link to the List Table The List Table has the following fields List Name, Customer Inputed Field 1 (CIF 1), CIF 2…CIF 8, a link to the Job Table (allowing only a single link), then there are Lookup Fields for CCF 1 Formula…CCF 6 Formula. Everything up to that point is pretty basic, but what I’m trying to do is create 6 fields CCF 1…CCF 6 that will actually perform the operations/functions shown in CCF 1 Formula…CCF 6 Formula. Here’s a link to the base. Link to Base I can get the CCF 1…CCF 6 fields to repeat the text of the formulas, but I actually want them to perform the operations within the text…can this be done? Thanks in advance for any guidance or assistance you can provide! Tom

I want to reference two fields in my IF but I just can’t seem to make it work… IF({Due date}>TODAY(),"+",“OK”), IF(Status=“Complete”, “Complete”, “OK”))

I need a simple formula for working out a suburb and it puts out a selected output. eg: if the suburb gorokan or toukley is in the field, the formula puts in KPM1. IF the suburb is hamlyn terrace it puts in KPM2 etc etc This will apply to 5 different outputs ( KPM1 KPM2 KPM 3 KPM4 LM1) based on the suburb. IF({Suburb}=“gorokan”, “KPM1”) This formula works great for the single suburb but I cant get it to output KPM1 if I put multiple suburbs in there I know it will be nested IF formulas but I just cant get this first one to work as expected. It will be simple im sure! lol

Hi! I’ve recently begun using Airtable for record keeping and project management. I have been able to do all kinds of fantastic things so far and am really loving Airtable. Most questions I’ve been able to find a previous discussion about and solution for, however, I’m stuck on this problem: I have two tables, [Tasks] and [TaskLog]. [Tasks] has a {Status} column, as well as a column for {StatusUpdated}, which lists every date that I’ve updated the status for a particular task. Example: If I updated a task from Pending to In Progress to Complete on three different days, it would include those three dates. On [TaskLog], I link to the individual tasks I worked on that day. This works well but has to be done manually. What I would like to do is for [TaskLog] to be able to auto-link to [Tasks] if any of the dates in {Tasks::StatusUpdated} matches the {Date} in [TaskLog]. Does that make sense? I know that part of the reason I haven’t been able to find the answer from searching may be that I’

Hi, Not sure where to start, would like to know if it’s possible to receive notification for users on the phone once a profile has been updated. Can Airtable write scripts? I’m using Thunkable mobile app.

Is there any way you can access a field in a different record? As an example, I collect records to manage fuel consumption. For each file of the tank, I make a note of the date, the car mileage, the total cost of filling the tank, and the price per litre. I need to calculate the difference between the current mileage and the previous mileage – is there a way this can be done? This is simple in both a spreadsheet or using a SQL Query against a normal database, but I don’t seem to be able to find a way using AirTable. Any help appreciated?

I’ve tried to follow along with the other questions on this topic, but I keep getting “Invalid formula”. I have three fields (that all link to another table). “Ayes”, “Nayes”, and “Absent.” If “Adams” appears in the “Ayes” column, the target field should be “voted for”. If “Adams” appears in the “Nayes” column, the target field should be “voted against.” And if “Adams” appears in the “Absent” column, the target field should be “did not vote on”. Here’s what I have: IF(Find(“Adams”, {Ayes}, “voted for”), Find(“Adams”, {Nayes}, “voted against”), Find(“Adams”, {Absent}, “did not vote on”) Thoughts?

Hello, I am getting below data, but I need to filter data where ‘Start Date’ is between 01-Jan-2019 till 31-Dec-2019. How can this be achieved? { "id": "recxxxxxxxxx",* "fields": {* "Priority": "N",* "Month": "2019-03",* "Week of the Year": "10th",* "Start Date": "04-Mar-19",* },* "createdTime": "2019-01-11T22:22:09.000Z"* }*

I have a sheet where interaction type field is a Single Select list Date of Interaction field is a date The following formula returns a date in the format 2019-11-05T00:00:00.000Z IF({Interaction Type} = “Meeting”,{Date of Interaction}," ") How can I get the date just to show as 05/11/2019

Goal: Calculate Character count limits for ppc ad sheet. Character Count field is the number of words in the copy for that ad Character Limit is the number of words allowed in the ad IF({Character Count} < {Character Limit}, “ :white_check_mark: PASS”) IF({Character Count} = {Character Limit}, “ :white_check_mark: PASS”) IF({Character Count} > {Character Limit}, “ :x: FAIL”) Please help! Thanks in advance

Hi everyone! I am having a hard time with some formulas due type of cells and blank cells. Hope you can help me. This is the Nested IF I wrote: IF( Cuota=PagoEnero, “Pagado”,IF(PagoEnero=BLANK(),“No” , IF(PagoEnero=0,Cuota-PagoEnero ,IF(Cuota-PagoEnero>0, Cuota-PagoEnero)))) The formula returns the same result if the cell is blank or is =0. I think this is because the statement will always be true in the second one but i dont know how to fix it My second issue is that, I do the same formula multiple times (Enero, Febrero…) and i need to summarize only the results that returns a number (not text) and make a sum For example: Enero=0, Febrero=No, Marzo= , … Returns: Enero CONCATENATE(IF(Enero=BLANK(), “”, IF(Enero=0, “Enero”, IF(Enero>0, “Pagado”))),IF(Febrero>0,"",“Febrero”)…and so on) I think due the column “Enero” is a formula does not recognize the result as a number, this is not working. I have tried to change text to number and it fixed but brings other problems. Could anyo

I have two fields (both are rollups - sum’s) that results in numbers: Projection Views Actual Views I have set up another field with this formula: SUM({Projected Views}-{Actual Channel}) But the formula returns a NaN. Can someone help me understand what the problem is here and how to resolve. The rollup fields draw on two separate tables, if that makes any difference.

Hi I am trying to group empty linked field but not sure which formula to use. Any help much appreciated. Thanks, Maria

I have a field that contains two strings that will always be constant: " - Device S/N: " and " - Dongle key S/N: ". I want to extract the serial number from in between these two constants. My biggest issue is that the character positions will always vary. Any ideas on how I can do this? MID, LEFT, MATCH, don’t seem to work at all. Here is a sample of the cell contents that I am working with: Device name: NewTom GO 3D - Device S/N: 708K0078 - Dongle key S/N: 1-1555622170

How can I translate the excel formula listed below into Airtable? I don’t see a COUNTIF or SUMPRODUCT fx option. I am trying to calculate the % a project is completed based on dates in multiple columns. Below is how I did it in excel: =IF(AY2="",COUNTIF(AS2:AX2,"<="&TODAY())*0.1667,COUNTIF(AS2:AY2,"<="&TODAY())*0.1429) Any help would be greatly appreciated!

Hi Im making quote with block When i add lot of text i need more than 1 page but i cant find a way to solve and make a quote with more than 1 page Can you find me a solution ? Thank you

Hello, AirTable community. I have an issue that I can’t seem to solve. One of my use cases for AirTable is both Work/ Cost Management. I create a new base for each new initiative. Within a base I have multiple tables; one which represents the master backlog where all Tasks are stored, and then individual tables for each person represented in the Master Backlog. Ideally, I would like to link the records to auto-update when edited from the tables that represent individual members of my team in the Master Backlog. I spend entirely too much time manually updating records and really need to find a solution to this. Any ideas? - Cheers and thank you ahead of time.

Hello! I’m trying to create a nested IF statement that uses a date field about an event {End Date}, to determine which quarter the event was in but I keep getting invalid formula errors. I am pretty well versed in excel formulas, but my knowledge does not seem to transfer to Airtable. Unfortunately, the quarters are weird and run Q1: 8/15-11/14, Q2: 11/15-2/14, Q3: 2/15-5/14, and Q4: 5/15-8/14. Ideally I would like this formula to continue to work for upcoming years without revision. Any ideas are much appreciated!