Skip to main content

Formulas

Leverage this space to unlock the power of Airtable formulas.

  • 5,944 Topics
  • 18,247 Replies
5944 Topics
J
Joshua_AdlerNew Participant
asked in Formulas
Using if formula to calculate prices based on various features

I’m trying to calculate a formula using the if function but not sure where to start. My formula correctly calculates the wholesale cost of a case of 12 bottles of imported wine from the ex cellar price. However, still wines under 14.10% Alc are taxed at 25% of the value (ex cellar x 1.25), still wines 14.1% and higher are not taxed, and to make things even more exciting, sparkling wines are never taxed. I want to make one formula that incorporates whether the wine is taxed or not. can anyone help? in my formula: ex cellar = price of 1 bottle of wine in EUR, 1.15 = est exchange rate 1 EUR= USD, 12 = number of bottles in a case, 18 = cost of shipping in USD to import the wine, 0.6 = target 40% margin on final price. everything works fine, just not sure how to incorporate the tariff on some wines but not others.

236
J
S
sam_samNew Participant
asked in Formulas
Formula problems

Hi @all, I am trying to move everything that has the “finished” checkbox checked to another table. Is this possible? I am using integromat’s automation system. here is what I’ve tried:

163
ScottWorld
E
Emma_MHKnown Participant
asked in Formulas
Trying to nest If conditionals and can't figure it out

I’m trying to figure this out and can’t for the life of me. Basically, this was a template in the AirTable Universe and when it’s a meal from leftovers I want it to say not just “Leftovers”, but which leftovers: ie “Leftovers (Beef Tacos)”. But I cannot for the life of me figure out how to do it. Here’s the formula that is currently in the table: I tried to do nesting IF statements and could not get it to work. I saw on a different post here that some people have conditional roll up fields, but I don’t seem to have that option (there’s no toggle on my roll up options to add conditions). Help is appreciated.

94
J
D
Daniel_TomaParticipating Frequently
posted in Formulas
Find Formula - Multiple Instances Of String

Hi All, I have a formula which finds a single instance of a string in a field but I would like it to find multiple instances of the string in the field, how should I change the formula? MID( {Field Name}, FIND( ‘beginning of string’, {Field Name} )+6, FIND( ‘end of string’, {Field Name}, FIND( ‘beginning of string’, {Field Name} ) )-(FIND( ‘beginning of string’, {Field Name} )+2) )

679
D
M
Michael_LeidensParticipating Frequently
posted in Formulas
How to add the Sum Column from Table 1, Table 2, Table 3 in a TABLE 4?

Hi, Thank you in advance for advise. I have 3 department in my company. I am creating 1 table per department. Table 1 = Product A Table 2 = Product B etc… I want to have a quick overview on all Sum from each table. I created a Table 4. I want that a cell displays the SUM of column A in Table 1 ; How to do it? Thanks

127
B
A
Albert_AndradeNew Participant
asked in Formulas
Formula to tally checkboxes?

I would like the # Field “T” to tally up all the checkboxes across the record. Is there formula for that?

31
A
K
Kendy511Known Participant
asked in Formulas
Formula sum different time format of all fields

I have 5 fields with different time formats which can be date, hours, minutes, seconds… ex: Field 1: 4 days Field 2: 2 hours Field 3: 2,5 days Field 4: 15 mins Field 5: 25 seconds Field 6 = sum (field 1 + field 2+ field 3+field 4+ field 5 ) with ‘days’ format Which formula I can use in this situation ?

12
K
V
Vickie_LimNew Participant
posted in Formulas
How to count total task per project

Hi there, Newbie here, so apologies if this has been asked. Scenario: I have 2 tabs (1. PROJECTS 2. TASKLIST), and I’ve linked each individual task items to a project on the TASKLIST tab manually. Lets assume the link between task-projects from TASKLIST tab is the main source of truth. Question - is there a better way to summarize the total count of tasks per project on the PROJECTS tab? Currently, I have to create two fields in PROJECTS tab. (1) task - to link all tasks connected to the project (2) total task count - use the count formula to add the count in task field. With this approach, I have to manually add each tasks to the task field on the PROJECTS tab, which seems repetitive since data is already on the TASKLIST tab, and also I might accidentally make a mistake if this is manual. Is there a way to lookup TASKLIST tab, and see the relation between task vs project on that tab, and do the count automatically in PROJECT tab? Thank you, Vickie

172
V
C
Contact_Lights_New Participant
asked in Formulas
Adding IF formula to a column with an existing concatenate formula

Hey guys! I’m looking to add an if formula to the formula below. What I need is that if the “fixture model #”, the “bulb model #” or the “miscellaneous” fields are empty, the output of the concatenate funtion is “N/A” CONCATENATE({Name (from Orders)}," - “,”(",Quantity,") “,{Fixture Model #},” / “,{Bulb Model #},” / ",{Miscellaneous}) Thanks in advance!

1715
Justin_Barrett
R
Robert_AndrewsKnown Participant
posted in Formulas
Count records since 7 days ago?

Table “Companies” is a list of companies. Table “Posts” lists blog posts, and has a Date field. They are linked related between Companies’ “Posts” field and Posts’ “Company” field. ie. Posts relate to companies. On the Companies table, I am able to successfully show a “Total Posts” count of the number of corresponding posts, by adding a Count field for the Posts table. However, how can I add another column to Companies which only counts those Posts whose Date fields fall within the last seven days? tl&dr - How do I count the number of linked records whose date falls within the last seven days?

21
ScottWorld
J
JonathanBInspiring
asked in Formulas
Logging the "history" of a checkbox field with a formula

Hi all I’m trying to find a way to record, within a table, an ongoing “history” of when a checkbox has been checked/unchecked. The first stage is simple enough, e.g. a formula field that outputs today’s date when the box is initially selected - IF({Yes/No}=“1”, DATETIME_FORMAT(TODAY(), “DD/MM/YYYY”), “”) but what I then want is an additional field that keeps a kind of tally of when the box has been checked/unchecked, i.e. “Approved 03/07/2020; removed 10/07/2020; approved 13/07/2020…” etc. I think it will probably involve using a “last modified” field but I can’t work out how to make the “tallying” part of the formula, rather than just updating each time there is a change. Can anyone advise if this is possible to do? Thank you!

283
ScottWorld
D
David_Gutierre1Participating Frequently
asked in Formulas
Running Countdown based on single select stage

Hello all! I want to have countdown (showing how many days left) based on the stage (a single select field) of a record. Each stage should take a certain amount of time to complete, so the goal is to see how many days left I have to complete a stage. Then once I change it to the next stage, that countdown is reset to the parameters of the next stage. I have parameters are mostly set by formula-based date fields that live in the table. So it would look something like this: If {Stage}=Stage 1, calculate difference between {date 1} & 14 days from {date 1} If {Stage}=Stage 2, calculate difference between {date 2} & 14 days from {date 2} If {Stage}=Stage 3, calculate difference between {date 3} & 28 days from {date 3} If {Stage}=Stage Complete, “Complete” I tried doing a DATETIME_DIFF inside of the If function, but have been struggling with errors. Seems pretty straight-forward, but I can’t figure it out… Any help would be great! Thanks in advance.

133
J
L
LayneNew Participant
posted in Formulas
Adding UPCOMING status to tasks that will be started soon

I have been using the really helpful formula in this airtable article to have a status of done, complete, and in-progress for my project management base: https://support.airtable.com/hc/en-us/articles/221104988-Conditional-formulas-with-emoji My formula currently looks like this: IF({Done} = 1, “ :white_check_mark: Done​:white_check_mark:”,IF(IS_BEFORE({Due Date}, TODAY()), “ :alarm_clock: OVERDUE​:alarm_clock:”,“ :thumbs_up: In-progress​:+1:”)) However, I would really like to divide the In-progress into tasks that are currently in-progress (keeping status. as is) and tasks that are upcoming (start date is after today, basically). This is what I want: :white_check_mark: Done :white_check_mark: : IF({Done} = 1, “ :white_check_mark: Done :white_check_mark: ” :thumbs_up: In-progress :thumbs_up: : Start Date=before today and Due Date=After today :alarm_clock: OVERDUE :alarm_clock: : Start Date = before today and Due Date=before today :clock2: Upcoming :clock2: : Start Date= after

21
B
J
Janet_YenNew Participant
asked in Formulas
How to track number of clicks to a link shared in a cell

Hi, I’m creating a table that includes links to external websites like facebook pages, instagram profiles, personal websites, etc. I’d like to track how many people click on each of the unique links. Is there a way to do that? Airtable’s Support team told me this is not a native feature. Anyone have creative ideas on how to do this? Thanks.

598
J
J
Jason_Burke1Known Participant
asked in Formulas
Formula To Return Age of Product in #.## format

Hello! I recently got into canning (and general food preservation). I have created a database which helps me manage my inventory levels of products I have canned (Example: Jars of: Carrots, Green Beans, Jams, Jelly, etc). In my table where I record batch information - I am recorded the date I created that batch of product. I’d like to have a column (called Age) that returns the age of the product (batch) in #.## format. For example: If I created a batch of product today, it’s age should read: “0.00” meaning its 0 years and 0 months old. If I created a batch 1 year ago, I’d like it to say “1.00” because it’s a year old. If I created a batch ‘days short of it being 2 yrs old’, I’d like it to say “1.11” because it’s 1 year old and 11 months. Any help would be greately appreciated.

77
J
C
Carlos_HurtadoParticipating Frequently
posted in Formulas
Add text before a word in a formula column

Hello everyone, I need to add text before each word in an already established Formula column. Example: Formulas Column Results : ENG, SPA, FRA, RUS, ITA I want to add “Title” and a underscore before each text resulting in: Title_ENG, Title_SPA, Title_FRA, Title_RUS, Title_ITA Current formula is: IF({Title (No Special Characters)}, “” & {Title (No Special Characters)}) & IF({Audio Languages}, “_” & {Audio Languages}) Thank you in advance!

281
Justin_Barrett
T
thej_deepNew Participant
asked in Formulas
Rool up field not working on my free account

ALL ROLLS UP I HAVE CREATED IN JUNE MONTH ARE WORKING FINE BUT WHEN I TRY TO CREATE SAME ROOLUP ITS NOT WORKING BEFORE I USE TO GET OPTIONS NOW I AM NOT GETTING OPENS FOR SUM, AVERAGE FOR REFERENCE I HAVE ATTACHED SCREENSHOTS

52
T
E
Erik_PaquetNew Participant
posted in Formulas
ARRAYUNIQUE not working

Hi, I’m trying to get unique text values using ARRAYUNIQUE but the result is returning duplicate entries. I have a lookup column called airlines with the values: American, Delta, Southwest, Southwest I want the result American, Delta, Southwest. But ARRAYUNIQUE({Airlines}) still returns American, Delta, Southwest, Southwest Any ideas?

161
J
J
John_DlouhyKnown Participant
asked in Formulas
Airtable Record's URL & Zap

Hello! I have built a :zap: Zap that sends colleagues an email reminder if the deadline for a task is past due. I am wondering if it’s possible to include in the Zap email an Airtable link to the specific record? This would enable the recipient to quickly click the link and be taken directly to the record for edits. Perhaps there’s a formula to add the record’s unique URL? Thanks much! :nerd_face: :surfing_woman:t5:

133
ScottWorld
E
Evan_DobosNew Participant
asked in Formulas
If cell contains A, write X - or - if cell contains B, write Y

Hey guys, I have hundreds of records that contain one of 12 combinations of initials. I want to create a new column that finds those initials and depending on what they are, write the entire name. For example: Column A includes: 2017-02-07.boe_.ag_.pdf 2017-02-07.bos_.ag_.pdf 2017-02-09.bof_.ag_.pdf I want the formula for Column B to find the initials, and write in the full name following these rules: if it finds “boe”, write “Board of Education” if it finds “bos”, write “Board of Selectmen” if it finds “bof”, write “Board of Finance” So, the end result in Column B would be: Board of Education Board of Selectmen Board of Finance Thanks for any and all help with this! If cell contains A, write X - or - if cell contains B, write Y

12510
Justin_Barrett
C
Currently_JasonInspiring
asked in Formulas
DATETIME_DIFF for a specific date

Hi, I have a formula DATETIME_DIFF(Date,28/06/2020,‘days’) This should give me the number of days between “Date” (a date field) and a specified date. I must have something wrong as it displays 18441 regardless of the date I specify. What do I need to do? Cheers Jason

12
C
R
Rian_CarrNew Participant
asked in Formulas
IF Statement Headache - Cannot seem to solve

I am trying to write a string of IF statements…so the logic is this: If user selects "Country’ as “North Vietnam” and/If the ‘Ultimate Destination’ is “Long Beach”, then the result should be the date column{North Vietnam to LAX}. This works. But… The problem is it won’t let me keep adding to the IF string. for example below: it won’t recognize the "China’ Query. I am really struggling with this one…any help would be so much appreciated. IF({Country}=“North Vietnam”,IF({Ultimate Destination}=“Long Beach”,{North Vietnam to LAX},IF({Country}=“South Vietnam”,IF({Ultimate Destination}=“Long Beach”,{South Vietnam to LAX} IF({Country}=“China”,IF({Ultimate Destination}=“Long Beach”,{China to LAX},IF({Country}="India ",IF({Ultimate Destination}=“Long Beach”,{India to LAX})))) This formula works, but I cannot add onto it: IF({Country}=“North Vietnam”,IF({Ultimate Destination}=“Long Beach”,{North Vietnam to LAX}))

227
J
P
Paul_WarrenInspiring
asked in Formulas
Formula to Condense a Series of Numbers with hyphens

I am looking to take a long string of comma separated numbers (it is a string from a look up). Here is an example of numbers to summarize: 1.17-01, 1.17-02, 1.17-03, 1.17-04, 1.17-05, 1.17-06, 1.17-07, 1.17-08, 1.17-09, 1.17-10, 1.17-11, 1.17-12, 1.17-13, 1.17-14, 1.17-17, 1.17-18, 1.33-01, 1.33-02, 1.33-03, 1.33-04, 1.33-05, 1.33-07, 1.33-08, 1.33-09, 1.33-10, 1.33-11, 1.33-12, 1.33-13, 1.33-14, 1.33-15, 1.33-16, 1.33-17 If you look closely, there are missing numbers. I am hoping to find a formula that will output: 1.17-01 --> 1.17-14, 1.17-17, 1.17-18, 1.33-01, --> 1.33-05, 1.33-07, --> 1.33-17 I am flexible on the “–>”, but as you can see a simple dash won’t work, because dashes exist in the numbers. Has anyone seen something like this before? Any help would be greatly appreciated!!

1611
B
J
Jan_Segier2New Participant
asked in Formulas
Does a date fall in a date range (between start and end date)

Hi 3 tabs: vacation: form > start and end date vacation + linked records to “employees” so they can select themselves employees projects: per record 1 day to work on a project + linked records to “employees” to select technician Question: is there any way to signal in any way (preferably not being able to select the technician in question) that the selected/to be selected technician is on vacation on the workday in question?

32
J
A
arielaronicaNew Participant
asked in Formulas
Is it possible to create a single-select column for grouping by an auto-compiled age?

I’m trying to create a single-select field for easier sorting based on a compiled age based on the Birthdate (DOB) column. Here are my formulas: Age column: DATETIME_DIFF(TODAY(),{DOB}, “years”) Which returns the age of each student. Age Groups Column: IF( {Age} <= 2, ‘PRE-K’, IF( {Age} = 3, ‘3-5’, IF( {Age} = 4, ‘3-5’, IF( {Age} = 5, ‘3-5’ ) ) ) ) Please tell me there is an easier way to do this lol. I want it to autocompile, but I’d like it to be single-select rather than return plain text for the visual aspect with color coding. I hope this makes sense! The “Age Group” column is how I would like it to autocompile, allowing me to delete “Age Groups.” Attaching a photo for reference!

41
ScottWorld

Badge winners

Show all badges
Terms & Conditions