Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,939 Topics
  • 18,228 Replies
5939 Topics
D
Dan_RuanNew Participant
posted in Formulas
How to validate zip codes against a list/table

Hello, I need to validate zip codes in applications against a list of zip codes within certain city limits. For example, if an applicant submits a zip code and it’s not listed in my other table containing a list of eligible zip codes, there would be a column flagging me something like need verification or ineligible. Is there a formula or some way to do this? Thank you!

91
ScottWorld
P
Product11New Participant
posted in Formulas
How do I extract data from a dictionary data type?

Example I have a field with the following data: {“blog”:“abc dot com”, “twitter”:“xyz dot com”, “facebook”:“pqr dot com”} I want to use the “keys”, viz, blog, twitter and facebook to extract abc dot com, xyz dot com and pqr dot com respectively. So if I have the key “blog” in a formula, I want to store the corresponding data ie “abc dot com”. Any ideas how to do this?

50
P
Cody_Winchester
Cody_WinchesterInspiring
asked in Formulas
Formula with OR and AND criteria

I have a Learning Course table that tracks courses with either a single session or dual sessions via checkbox fields. I have links to fields in this table that identify whether an enrollee has completed the relevant sessions(s). From there I have a checkbox field that indicates whether, based on the attendance, whether an enroll has effectively completed the course. I tested a formula field that runs the checkbox field for the dual session completion, but I’m struggling to create a formula that accounts for the single session field. I initially thought it was an IF(OR(AND())) type of configuration but nothing seems to work. Here’s what I tried: Works: IF(AND({Session 000 Output}>0,{Session 001 Output}>0),{<TRIGGER> Completed Course}=1,{<TRIGGER> Completed Course}=0) Doesn’t Work: IF(AND({Single Session}=1,{Session 000 Output}>0),IF(AND({Dual Sessions}=1,{Session 000 Output}>0,{Session 001 Output}>0)),<{<TRIGGER> Completed Course}=1,<{<TRIGGER&

132
Cody_Winchester
K
Kamil_KrezelNew Participant
posted in Formulas
Regex extract - multiple matching strings

I have fields with extracted text from a contract and what I need to do is extract every vehicle registration number in the text field. The formula I have will only extract the first valid number. Thanks in advance!

70
K
J
Jamie_RoodKnown Participant
asked in Formulas
Prefill Form only takes the first word in my field

Hi, I am trying to prefill a form using the following formula: CONCATENATE({Applications URL}, ‘?prefill_Which+role+are+you+applying+for?=’, {Name of opportunity}) The ‘Name of opportunity’ field contains more than one word, but the formula is only pulling the first word. Here is an example of one form’s URL: Airtable Apply to our roles today! - Airtable Fill out the "Apply to our roles today!" form quickly and securely on Airtable. When the ‘Name of Opportunity’ is actually ‘Logistics, Freight and Warehousing - DSV’. How do I ensure that the form populates with all the words? Thanks!

86
J
J
J_VNew Participant
asked in Formulas
Filtering on Rich Text on long text field

Hi Airtable Community, We are using the “enable rich text formatting” on a “long text” field, using bullet points to indicate points of attention or statuses. Is there any way to use this rich text and do the following: Filter out first / last 3 bullet points by using a formula? Filter out items under a bullet point matching a certain category, eg. text in bold - by using a formula? Screen shot attached where I’m looking if I can filter out basis eg. “Week 13” or filter on bold. Many thanks!

205
Justin_Barrett
A
AL2New Participant
posted in Formulas
Method for creating new records and updating records with forms

I am attempting to collect data from employees that tracks incident reports over time. This roughly works as follows: Form1: "Report New Incident" This form collects information about an incident such as location, date, classification, etc. and assigns it a new ID. ID | Date | Location A | 1/1/19 | Plant X B | 1/15/19 | Plant Z Form2: "Update Incident" This form asks the user for an incident ID and then some additional information is added to the original incident (new information or updates to old information). ID | Date | Status A | 1/3/19 | In progress A | 1/5/19 | Solved The idea is, the employee who is updating on an incident does not need to reenter information (such as location, classification), but should provide some updated information (update time, current status). Having two tables ensures they contain unique information with the exception of ID. Unfortunately, I can’t join together based on ID with some sort of JOIN query. Using lookups partially solves this, but then it g

133
Julian_E_Post
E
Edd_TillenInspiring
posted in Formulas
Looking up without a link field

This is sending me nuts. I feel like there should be a way of doing this, but can’t get my head around it. I’ve got one table with Risk Assessments on, and another with Risk Treatments. I want to be able to start a record in the Risk Treatment table, and then use a link field to say which Risk it’s associated with. So far so good. Now I want a Risk Treatment field in the Risk Assessment table to show which risk treatments are associated with that risk. At the minute, whenever I add a risk treatment, I have to manually add the risk in the Risk Treatment table, and then go back and manually add it to the Risk Assessment table too. The problem I find is that the Risk Assessment table doesn’t link to Risk Treatment table until I manually create complete the link field. But the ref number field matches the linked Risk field on the Risk Treatment table. So that relationship has been established, but it appears to only be one way. See, I’m talking myself in circles now, I just can’t get my he

31
kuovonne
R
Rob_JamesonKnown Participant
posted in Formulas
Subtract values by Record ID?

Hi everyone, I’m trying to solve a basic issue with Airtable formulas… I want to subtract the value of one cell from another. For example, in excel, it’s subtracting cell E2 from E3… In Airtable, I learned about the Record ID that I can get from the cell URL, however I don’t see examples of how to write a formula referencing the values of that cell. Can someone show me how to subtract the values of 2 cells, that are each specified by a Record ID?

409
ScottWorld
L
Lucie_TennisNew Participant
asked in Formulas
Formula to Automatically Update Based on Months/Days

Hi everyone, I have a fairly complex base tracking financials, burn rate, and runway. I have run into a snag trying to come up with a formula that will automatically update based on what month of the FY we are in (for burn rate). For example, I have a table specifically for burn rate. If I want to see our burn rate YTD, I have to manually update the formula with the current month (burn rate = (starting balance-ending balance/# of months)). Starting balance - ending balance is an easy part but currently I need to manually update the divisor (our FY is from Jan to Dec, so right now I am dividing it by 8.5). How can I get it to update automatically? Can anyone help?

80
L
O
Olivia_DiGiusepNew Participant
posted in Formulas
Is there a function for calculating the sum of 2+ COUNT columns?

I have a two separate Count columns and I’d like to add them together to find the total count. Is this possible?

562
O
L
Lindsay_KirschParticipating Frequently
asked in Formulas
IF statements and formulas

Hello! I am trying to make a formula based on an IF statement. IF one cell = something, then perform this formula. For example, IF( {Frequency}, ‘Annual’,({Cost/12})) I would like to have multiple “IF” statements & formulas. Is this possible?

2217
L
N
Nikolas_Da_FonsNew Participant
asked in Formulas
Formula to count days from one specific date

Hello guys ! So, i would like to create a formula that count days from a specific date. Something that would add 1 everyday. I hope im expressing my problem correctly :nerd_face: Thanks

1964
N
B
Brett_SizelandNew Participant
posted in Formulas
Comparing Cells from 2 entries

Hey after some help on the best way to calculate a total from 2 different entries in a table. So I am trying to develop a scoring/judging system for a school project. One of the sections is they submit a deign and they have to submit 2 identical copies and we mark them as A and B and we check them both against a set of regulations. I currently have a table that has the team listed twice one entry is for their A copy and the other entry is for their B copy. There are over 30 regulations they need to comply with, I have a column that totals the points scored for A and B separately but I need a way of combining them. I can’t just add them or do an average or take the lowest score. The way we judge it is if they fail a regulation for both copies they only incur the penalty once. If they fail it with only one copy then its still just the penalty once. What can happen is copy A fails regulation 5 and copy B fails regulation 7 for example I need a way of comparing them and finding if they fai

104
TheTimeSavingCo
R
Rob_JamesonKnown Participant
asked in Formulas
Problem calculating percent of survey responses when no answer given

Hi everyone! I’m new to learning how to use the deeper aspects of Airtable and how to use linked records. The problem I’m having … I have a multi-question survey, and am trying to calculate the percentage that people answered a question “yes”. However, some people skipped the question, so I need to remove these from the % calculation. My goal is to determine what percentage of responses were “yes” from those who actually responded. I tried by making a lookup table for each question that would only show answers yes/no (so no blank answers), and then the formula COUNTALL ({my lookup field name}) to first tell me how many responded, but I am not getting the correct answer. Does anyone have recommendations for how to solve this? Thank you I

76
M
L
Louis_kwokNew Participant
posted in Formulas
Extract number?

Hi Expert, i have a field which keep a product volume, for example , 20g, 30ml, 45piece, 150l, 8pack. i just want to extract the numbers, so the after formula field will be , 20,30,45,150,8. i try to think and search for a while but have no idea how to do it , any advise?? thanks

73
L
S
SG_SoCalNew Participant
posted in Formulas
Formula for finding matching field values and returning those in one place

Hello, I am trying to use Last Modified Date for particular fields to evaluate whether those fields were updated today, and if so, concatenate those updated values, separated by commas, and output those into my formula field. The final formula result will be included in a daily email digest of changes to my database, intending to show the recipient which of the specific fields were updated. I first tested the OR function by creating a simpler formula just to make sure I had the right idea, and this does return “Updated today!” for the one record that I updated today, with “No update today” returned for records that don’t match (the ERROR values are due to many of my records not having Last Modified Times yet, as those fields are new). I initially had trouble getting the dates to match up properly before realizing that I needed to convert the Last Modified Time fields via DATESTR() to make sure that I was matching text strings only. My IF/OR formula, working as intended: IF( OR( {LM DS

694
S
B
Bora_GudenNew Participant
asked in Formulas
If Company A from Table 2 is the same as Company A from Table 1, add a green emoji to the row of Company A

Table 1 is a list of clients with attributes. The table is as follows: | Company Name | Contacted? | … | … | | Company A | :green_circle: | … | … | Table 2 is a list of interactions with clients The table is as follows: | Contact person | Company Name | … | … | | Jeffe Bezos | Company A | … | … | Company A in both tables are matched together (as in I select from the drop down list the Company Name in Table 2). How would I make the “Contacted?” column in Table 1 automatically add this green emoji, if Table 2 has a match of the Company?

13
Kamille_Parks11
A
Angelena_ZeiserParticipating Frequently
asked in Formulas
Two IF formulas

I currently have this: DATETIME_FORMAT({Date of donation}, ‘M/D/YYYY’) & “—” & IF({On behalf of corporation?}, {Donor’s employer}, Donor) I have another field named “Member Donor”. If the Donor field is blank then I want it to pull the Member Donor field.

44
TheTimeSavingCo
H
HS_789New Participant
posted in Formulas
Combining results of IF()

Hi All, Having trouble figuring out how to use IF, AND, concatenate for this situation: If {Field 1} then return “Mary”, and If {Field 2} then add “Bob” to the end of “Mary”, and then If {Field 3} then add “Jake” to the end of “Mary” and “Bob” It continues for all combinations of Field 1,2, or 3. This was one attempt: IF( {Field 1}, ‘Mary’ IF( AND( {Field1}, {Field 2} ), ‘Mary and Bob’ ) )

77
kuovonne
D
Dustin_GoodKnown Participant
asked in Formulas
Having issues with IF formula

I’m building a credit card base where we store expiration dates. One formula will simply tell us that a card is set to expire (aka when status is “Expiring”) within the month. At this point I have an automation set to email client with expiring card message. Now I’m trying to set up a second automation triggered by a formula field to let me know what the card is now “Expired”. So far this formula works to tell me if the card is Expiring or Valid. IF({Expiration Date}<TODAY(),“Expiring”,“Valid”) However now I want to add an “Expired” status too. Yet when I do this entire formula it doesn’t work. IF({Expiration Date}<TODAY(),“Expiring”,“Valid”), (IF(DATETIME_DIFF(TODAY(),{Expiration Date},‘days’) > 30, ‘Expired’)) What am I doing incorrectly?

105
A
M
Matthew_ChattwoInspiring
asked in Formulas
Badge system w/in training program

Hello community! I’m trying to implement a simple badge system within an volunteer program. Basically, when volunteers pass certain checkpoints I’d like to award them a badge e.g. ran workshop X >> badge 1 ten total workshops >> badge 2 The pivot table extension creates a visualization of what I want to do where… row = volunteer name and columns = count of each workshop But I want this to perform this calculation within the base itself (perhaps using arrays in a formula field?) with badges being awarded via once certain conditions are satisfied. If there’s anyone out there that thinks they might be able to help but don’t yet have enough information pls let me know and I would be happy to try and explain further. Thanks very much, Alex

370
M
rrbbtalnorth
rrbbtalnorthParticipating Frequently
asked in Formulas
"OR" and "AND" search formula together

Hi, I have a database of professionals for whom I want to create a formula to search for the appropriate therapist using MAKE There are categories where I want to search all searchable categories, and there are categories where I want to search one or another category. for example- I am interested in looking for a therapist “for children” in the area or “Tel Aviv” or Jerusalem. AND( SEARCH(“children”,{categorys}) OR( SEARCH(“Tel Aviv”,{categorys}) SEARCH(“Jerusalem”,{categorys}) ) ) But apparently the formula is incorrect. Would appreciate help

657
ScottWorld
H
Hiteshree_RuparNew Participant
posted in Formulas
Count linked records over multiple fields in the same table

The Count field lets you count the number of linked records. For example, take the screenshot below Line 8, I want the count field to count the number of linked records in Team 1-4 and then add them up. Is there a way to do this? Seems like this should be easy but may have reached the limit of Airtable. This is essential to the business use case I want to get Airtable for my organisation. Any work arounds are appreicated too.!

223
R
T
Ted_Charles_BroNew Participant
posted in Formulas
Formula for Lookup to return only one option

Hello all, Looking for a formula that takes input from another field to determine which item in the lookup to return. For example; LOOKUP FIELD | OPTION FIELD | RETURN FIELD Apple, Orange | 2 | Orange Apple, Orange | 1 | Apple Thanks all

222
ScottWorld

Badge winners

  • First Reply
    GegHhas earned the badge First Reply
  • Welcome
    youssefhas earned the badge Welcome
  • First Topic
    _Chris_has earned the badge First Topic
  • First Topic
    GegHhas earned the badge First Topic
  • Welcome
    blessing333has earned the badge Welcome
Show all badges
Terms & Conditions