Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,938 Topics
  • 18,225 Replies
5938 Topics
A
Admin_CharriolNew Participant
posted in Formulas
How do I search two fields for instances where values match?

I have two fields full of product reference numbers that I want to compare for matches. The first is called Reference . This is a long list of stuff we make, some of which are out of date and out of stock. The second is called OldReference , which contains just the old items with remaining inventory. I want to search the two fields for instances where the reference numbers match, and then mark them somehow. I assume in a third field that returns 1 or 0, true or false, yes or no… Anybody know how to do this using a formula, lookup field, or some combination? Thanks in advance to anyone with a good idea. :grinning_face_with_big_eyes: -Frank

734
A
A
Adi_MenayangKnown Participant
asked in Formulas
Can I turn formula fields using IF(FIND) into a numeric field? (Or alternatives)

Hello, I’m trying to summarize information in a Linked Record field type collected through Forms in which respondents can select multiple linked records. We invite respondents to select their Subject Area Expertise, options provided are categories from a sector-wide set of standard taxonomy. The Subject Area taxonomy is organized in a hierarchical manner. For example: Level 1 (Category): Health (SE000000) Level 2: Nursing Care (SE060000) Level 3: Home Health Care (SE60100) Level 3: Hospice Care (SE60200) Respondents can select from the whole list without seeing the hierarchy. I would like to generate charts that summarize and collapse respondents’ answers. Using the example above, I would like the chart to collapse all individuals who selected “Home Health Care” and “Hospice Care” to fall under the bar/pie chart slice for "Health" I tried using a lookup field for each Subject Area’s category, but it duplicates responses In the example below, the row with three different “Environment”

83
A
Cole_Wedemeier
Cole_WedemeierInspiring
asked in Formulas
IF formula only working on first of 3 nested "IF" statements

I currently have the following IF nested statement. I don’t get an error when saving, but it only works when the first IF is found (13) and produces nothing for a result when the other two are in the identified field to check. Am I missing a key element to make the “false” portions of each IF statement work with a new nested IF statement? IF({⛔ Extender Weeks}=13, SUM({👌 WEEKS in Program (26 = 6mo)}+13, IF({⛔ Extender Weeks}=9, SUM({👌 WEEKS in Program (26 = 6mo)}+9, SUM({👌 WEEKS in Program (26 = 6mo)}+4)))))

296
Cole_Wedemeier
M
Max_LorenzNew Participant
posted in Formulas
Create UTM link from several fields

Hello, I am in despair, I want to build a table, with UTM left. The UTM links are fixed, but still need to exchange names and discount code. In google Sheet this was totally easy, but in Airtable I just can’t get it right :frowning: Here are the fields, the function should take: https://www.website.com/?utm_source=instagram&utm_medium=influencer&utm_content=LINK_TO_FILED&utm_campaign=webseitecampain&utm_term=websiteterm&discount=LINK_TO_FIELD Thanks for your help

10
M
J
John_V_WillshirNew Participant
posted in Formulas
Formulas pointed at Multiple Select - IF commands, or something else?

Hi there. I have a multiple select column that contains seven audience types, which have been added in a different order (no set pattern). I also know how big these audiences are (eg Generalists = 4406). I’m trying to write a formula that returns a total audience based on whatever has been selected in that multiple select box. EG if “Generalists” and “Digital” have been selected, in any order, it counts 4406 + 106 I can write an IF command to create a value for an exact match in the box (e.g. ONLY says “Generalists”), but stuck on how writing this more complete form. Any help gratefully received!

81
Dimitris_Goudis
R
Rob_CobbNew Participant
posted in Formulas
Leftpad using Rept and Len

I’m making course codes (like ACT015) from numbers (like 15) and prefixes like “ACT”. The trick is - how to left pad the numbers with 0 when combining them? The part of the formula to add the right number of '0’s: REPT(“0”, 3 - LEN(Number&"")) and the whole formula: CONCATENATE(Prefix, REPT(“0”, 3 - LEN(Number&"")), Number) Hope this helps someone! I didn’t see any other posts on the first page of results when I googled “airtable formula leftpad”, so had to get creative.

130
R
L
LaTrelle_GomezNew Participant
posted in Formulas
Calculate end date using number of days calculation excluding weekends

I believe the “SWITCH” function was probably not the proper tool for what I am trying to do. Here is what I did: IF({Workflow}=“Manufacturing”, DATEADD( DATEADD({Start Date}, {Estimated Production Days}, ‘days’), SWITCH( WEEKDAY( DATEADD({Start Date}, {Estimated Production Days}, ‘days’) ), 0, -1, 1, -2, 0 ), ‘days’ ))

10
L
K
Kevin_MoreauKnown Participant
asked in Formulas
Simple formula returns field with additional brackets " "

I wrote a very simple formula for my first field column to include fields from other columns in my table. I have only two fields in my formula with text strings to include spaces between them. My issue is that one of the field ends up between two brackets like this " ". As you can see, the field Prix Fab Gastier is = 12" BW STD in the table, but in my formula it returns “12"” BW STD" So why is there in total 3 additional " ? One before the 12 One after the 12" and one after the STD

313
K
F
Fadwa_MajedNew Participant
posted in Formulas
How to make formula where the text value is the condition

Hello, I’m having troubles figuring away the proper way to make a formula that will: "if the status = canceled or replaced, the value of the field should be = quantity-quantity (making it a 0) " where the status is a single select field, how can I make this work? Many thanks in advance

21
J
C
Connor_ForsythNew Participant
posted in Formulas
How to calculate growth rate of attendees?

I am running an event/webinar series, and I’m wanting to identify the total number of people attending the event for the first time (as to get an indication of growth). I have two linked bases The event series the attendees Key information I believe required (that I have): Date of each event Event name Attendee name Attendees assigned to events Total attendees at the event My thinking was to create some sort of formula as you see in this image below: (However, you cannot dynamically link a date from the base into the date field in this image. ~ Essentially my logic is: Count attendees if this is their first event. – Get dates of the events –List dates that attendee was at an event. – Get an event date –If the attendee Does not include dates before event in testing. Count. ~ Aggregate and sum of growth is printed. Any tips on how I can make this happen?

70
C
M
Mariah_MalczewsParticipating Frequently
posted in Formulas
Multi-item selection lookup formula? or: Formula that can handle multiple data points in one cell

I work for a small college, and we want to give Airtable a try for tracking our applicants and student financials. I’m running into problems when students are registered into more than one program, notably our “double diploma” program, which is actually two programs bundled together. (I also want to see if I can use it to make our scheduling simpler, so the Program Group is an important level for that) The problem comes when I’m trying to calculate the amount of tuition that a student owes at any specific point in time. If I lookup Tuition costs for a student, I get two results concatenated, and the formulas do not like this. Even if I can get the formula to calculate so that I get concatenated answers, I can work with that (ex. Tuition 1, tuition 2—formula magic—>due “now” (actually start+/-weeks) 1, due “now” 2 ) If I set up another table, from what I understand, I have to go through and manually enter the links. I’ve already made links between three different tables at this point

267
M
Shawntel_Cote
Shawntel_CoteNew Participant
asked in Formulas
IF/OR Statement involving a single select field

I’m looking to create a datestamp field based on if a Single Select field is either COMPLETE or CANCELED, otherwise it should remain empty. I’ve searched through this site and tried a variety of options but have had no success. IF(OR(Status=“COMPLETE”,“CANCELED”,LAST_MODIFIED_TIME())) **This returns all cells as blank Thanks in advance!

100
Shawntel_Cote
T
Tobias_LGKRKnown Participant
asked in Formulas
#ERROR! on very simple SUBSTITUTE

Hi, I am getting an Error back on the very basic SUBSTITUTE, which I have used numerous times in other tables of my base. SUBSTITUTE({Record Name}," “,”-") {Record Name} is a FORMULA FIELD that pulls the text from a LOOKUP FIELD (in the same table) that pulls the text from a FORMULA FIELD in another table. Any ideas? Thanks in advance. Tobias

343
T
F
Florent_BaronNew Participant
posted in Formulas
Rollup with product not sum

Hello, I’m NEW to Airtable (so TY for being patient & detailed) I have a base with 2 tables in it, Item & Value. I’m trying to collect all of the notes for each items. All of the tables are linked, and I have been able to bring each of the notes into one cell (example: 0, 17, 37). But now I need to multiply those numbers together not sum them. I can’t do a PRODUCT (Values). Here my setup Thks

102
Ben_Young1
H
HSY-RParticipating Frequently
posted in Formulas
Lookup a number in a sliding scale table

Hi, Link This workbook has a sheet called ‘Test’ which has fields that include stops & mileage. These are the x & y axis fields of the associated table ‘Sliding scale’ Y = mileage x - stops I am struggling to write a formula that can find the associated rate based on the number of stops & mileage. Note, that mileage could be 112 and stops could be 73 so the formula would need to look in between nearest numbers to associate the correct row/column combo. Answer to above should be 2.07 Can anyone help? Please

85
kuovonne
I
Ilias_TParticipating Frequently
posted in Formulas
Fill a field with the value of another fild on a different record

What I’m trying to do is what I describe in the title. I have a field with a value. What I want, is to transfer that value to a field of ANOTHER record on the same table. Of course I want this to happen only if that value is a negative number, but this is not my problem.

00
I
M
Marcus_FaulwassNew Participant
posted in Formulas
Splitting a link to multiple records into multiple lines in page designer

Hi, in a link to multiple records field, i collect several strings (image urls) to one product output of this field {Images} in page designer would be like “url1, url2, url3” I need to split these urls linewise in page designer, some boilerplate added, e.g.: “url1.jpg” “url2.jpg” “url3.jpg” While i have several ideas on how to solve this in the event of just one string, iterating the array of multiple records is a challenge (at least to me). Any ideas? Thanks in advance! Best Marcus

298
M
M
Malena_SparNew Participant
posted in Formulas
Help: Formula for transferring data to table when 2 items are selected in another table

Hello! I am looking for help creating a formula for data entry automation. Screenshot 1: Table: Activities Category column: Multiple select Grants column: Links to another record Workplan project column: Links to another record Screenshot 2: Table: Grant 1 Deliverable: Long text Activities: not sure what field type to use, formula? Workplan projects: not sure what field type to use, formula? I will enter data in the “Activities” table, including 1 or more categories for each activity, 1 or more grants for each activity, and 1 or more workplan projects for each activity. In the example, I have titled the activity “test webinar” categorized it as “webinars” made it apply to “grant 1"” and “WP project 1.” Since it applies to Grant 1 and the “webinars” category, I would like it to then automatically enter the Activity Name under “Activities” in the “Grant 1” table in the “conduct at least 5 webinars” deliverable column. I would also like it to automatically transfer the accompanying workpl

41
Kamille_Parks11
Z
Zeke_FraintParticipating Frequently
posted in Formulas
Edit specific cell in formula field

Is it possible to edit a specific cell of one record in a particular formula field?I have, Contract Start Date and Contract End Date (formula of Start + 1 year). Some contracts are 18 months or 2 years. Can I adjust those specific instances accordingly? Thanks!

194
Justin_Barrett
9
908_InteriorsNew Participant
asked in Formulas
If both date fields contain a value, return "available"

Hi there, I have accomplished a few formulas through my airtable base but I have trouble with this one: In a library, I want to return “not in stock” for items that are signed out and have not yet been returned. In writing this would be: If SignOutDate contains a value and ReturnDate contains no value, return “not in stock”, otherwise “available” IF( AND( {SignOutDate}<>””,{ReturnDate}=BLANK() ), ”notinstock”,”available” ) As I am new to the AND/IF business, I might have severe problems in here already? If not, could it be that the date fields are the problem, they are set as ISO? Any help highly appreciated. Best, Sisi

102
9
S
Stoober_PickupNew Participant
posted in Formulas
Cause action (from Zapier) when time from created date elapses

I’m trying to send an email (using Zapier) to a contact once a record has been on the page for 1 week. for example: someone submits a report- record is added. Once 1 week elapses a field changes to trigger a Zap to send an email to a contact. Any ideas of how?

131
Ben_Young1
Joe_Svingala
Joe_SvingalaKnown Participant
asked in Formulas
Help: Find MIN() Date for each month in a date time field & dateadd -28 weeks

I know that I am probably approaching this formula wrong, or maybe it isn’t possible with the way MIN()/Dates/Fields work in Airtable - but I figured I would ask the community for a little help on this one. Context: Need to assign work order due dates based off content Air Date. More specifically, I am trying to take the earliest date found for each month then subtract 28 weeks (dateadd -28 weeks), and use that Min Date - 28 weeks as the Work Order Due Date for every record that has an air date with the month that calculated it. Example: Record | Air Date | Work Order Due Record 1 | 04/22/2023 | 9/19/2022 Record 2 | 04/14/2023 | 9/19/2022 Record 3 | 04/03/2023 | 9/19/2022 Record 4 | 04/19/2023 | 9/19/2022 Record 5 | 07/22/2023 | 12/29/2022 Record 6 | 07/25/2023 | 12/29/2022 Record 7 | 07/13/2023 | 12/29/2022 In the example above, the four records have an Air Date in April, but the formula would calculate the Work Order Due Date as 28 weeks prior from 4/03/2023 since its the minimum val

882
Joe_Svingala
D
Dev_OpsNew Participant
asked in Formulas
Rollup smallest date/time past now

This might be simple, but I fail to get my head around it. Help, please. We have 2 tables; Profile - name, image, etc Availability - date/time, profile linked record 13/2/2022 4:57pm 20/2/2022 4:57pm 23/2/2022 4:57pm 11/2/2022 4:58pm On Profile table, we need to roll up the Min(values), but only display date/time past NOW. If now date/time is 19/2/2022 4:45om the correct answer needs to be: 20/2/2022 4:57pm

02
D
Valentino_Escal
Valentino_EscalInspiring
posted in Formulas
Adding date/s to a specific 'single or multiple' select field

I want to make a formula or automation to do the following task: Step 1 When a single or multiple select field has a status “Revisit” :two: Step 2 I need to automatically have a :four: Date 2 for the specific record with the status “Revisit” :two: , 30 calendar days after :three: Date 1. Note 1 The issue I face when trying to create a formula field is that the secondary date applies to all records, which I don’t want. Just for “Revisit” :two: . Note 2 I am not too sure if I can create conditional logic automation to perform this action. This is what I have attempted to no avail. Trigger When a record is updated with the status :two: “Revisit” Conditional Action If :three: Date 1 is not empty Update :four: Date 2 +30 Days Any Suggestions? This is the structure to the logic: :one: Address (Main) :two: Single or Multiple Select Field :three: Date 1 (European) :four: Date 2 (Formula or Automation dependent on :three: )

133
Valentino_Escal
N
Nina_CasalenaNew Participant
posted in Formulas
IF statements + calculations

Hi community! I am trying to track the status of payments which are made in multiple installments. For instance, we may make a payment for $400,000 but pay quarterly installments of $100,000. I would like to have the Total Paid field auto calculate using the payment schedule in the table. Total paid would equal {payment one} + {payment 2} + {payment 3} + {payment 4} but only when the corresponding payment status field has been marked as “complete” AND the corresponding payment date be in the current year (01/01/22-12/31/22). Any ideas? Thanks!

82
N

Badge winners

Show all badges
Terms & Conditions