Formulas

Hello! I am new to creating formulas in Airtable, and therefore need help creating a total count field based on multiple selections. What I want to have is the top items ordered (maybe top 15 items) and show which employees have ordered that item. I've created an Interface but it currently shows ALL the items and I haven't figured out a way to display only the highest totals of items. Also, I need to have a filter where when the item has the majority of the same words, it combines those two items together as one item (we did a "sale" so some items have a "25% off" in front of the item name).    Please help! 

Hello all, I have a single base to use as my CRM. A tab for Leads and one for Opportunities. They each have their own statuses. Lead status has 8 steps. Opportunity status has 6 steps. What I am trying to achieve A formula counting how many records are at a certain status (i.e Demos) in that month, to output that into an interface. A formula that tracks how many days it takes to go from status 1,2,3 to 4. It may be 1 to 4, or 2 to 4. A formula that counts, in days total time from status 3 (in Leads tab) to status 16 (in Opportunities tab) What i Have so far Following this airtable article, I have the automations to output when a status has been modified and outputs into a column for each step. When attempting to create a formula, I get an error. (no ,its not the emojis, I tried without, same error). But this formula would only count from step one step to the other, and not from any to a specific step. Thanks in advance :upside_down_face:

I am using ArrayUnique(values) as in a rollup field and then referencing this in a make.com scenario to output to a Word document. This works, but the output is a bit unfriendly.Products Acer_Vero B7_B227Q,Acer_Vero B7_B277,Acer_Vero B7_B247Y  I have tried changing the formula in the rollup field to something like ARRAYUNIQUE(values)&" - " but it has no effect.Is there a way to format the output to make it a little more readable - the ideal would be separate lines. EDIT: I have found how to add spaces etc usingARRAYJOIN(ARRAYUNIQUE(values), ', ')but is there any chance of adding a newline character (13) ?

For context, monitoring product listings on Amazon.I have 4 tables:Brands, with a lookup field to ASINs, from CatalogCatalog (containing ASINs), with a lookup field to OffersOffers, showing every offer on each ASIN, where each offer has a Seller ID, a lookup from Storefront Profiles.Storefront Profiles: Contains Seller ID.I'm trying to count, at Brand-level, the number of unique sellers associated with their ASINs. I've tried all sorts of counts and lookups, but can't get the right number. Either counting, duplicates, spaces.So far I have:In Catalog: A linked record to Offers, showing all offers per ASIN, with a lookup to show the active sellers.In Brand: A linked record to Catalog, showing all ASINs per brand, with lookups to show the offers and active sellers.Brand - 'Active sellers' - shows unduplicated sellers. Using COUNT(ARRAYUNIQUE({Active Sellers})) in a formula gives me 0.In Brand: 'Sellers' - A rollup of Active sellers using ARRAYUNIQUE(ARRAYFLATTEN(Active Sellers))

I am trying to write a formula field that will summarize the responses of three separate single select fields (Prototype 1 Cost Status, Prototype 2 Cost Status, and Prototype 3 Costing Status) so my costing team can have an idea of what proportion of the current prototype stage is "Meeting Target" or "Not Meeting Target". I'm finding this difficult because if we move from Prototype 1 to Prototype 2, we will need the response from Prototype 2 to override the response for Prototype 1 (as shown in the attached png) and so on for Prototype 3. This is what I have been able to draft so far: IF(  AND( {P1 Cost Status} = BLANK(),    {P2 Cost Status} = BLANK(),    {P2 Cost Status} = BLANK(), BLANK()IF(  AND( {P1 Cost Status} = "Green - Meeting or Exceeding Target",    {P2 Cost Status} = "Green - Meeting or Exceeding Target",    {P2 Cost Status} = "Green - Meeting or Exceeding Target", "Meeting Target")IF(  AND(

Hi,I have a formula that currently works 90% of the time which finds the number of days between a date range for a given month. The issue is, when the end date, ends in the month I am looking for, example below Oct the formula is correct (row 1, 3 and 4 in the image below). However, when the end date includes BUT does not end in the given month (example: row 2 or 5 in the image below) I get 30 days. I want it to show as 31 days because the date range is inclusive of Oct 2024. Any suggestions? Here is the formula,IF(AND({Start Date}, {End Date}),IF(AND({Start Date} <= DATETIME_PARSE("10/31/2024"),{End Date} >= DATETIME_PARSE("10/01/2024")),DATETIME_DIFF(DATETIME_PARSE(MIN(VALUE(DATETIME_FORMAT({End Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/31/2024"), "X"))), "X"),DATETIME_PARSE(MAX(VALUE(DATETIME_FORMAT({Start Date}, "X")), VALUE(DATETIME_FORMAT(DATETIME_PARSE("10/01/2024"), "X"))), "X"),"days") + (MONTH({End Date}) = 10),0)) 

Hello,I'm looking for a way or formula to set the records vertical within a cell as I show you in the attached image. Bests,L

Hello community,I'm building some kind of table where it has repeated content in the rows that I would love to have them combined, similar to a merge cell in Excel or Google Spreadsheets.Is it there a formula or a scripting that can help me have some structure like in the attached image?Thanks!Leandro

Hi I am trying to figure out how to remove the NaN value when the formula is 0/0I need it to reflect 0.  I have tried IF() and IF(And( formula's but I cant seem to get it to work current formula is {Surveys Recieved-MGR}/{# of Managers} Does anyone have any solutions?Thanks !

Hi All,I'm attempting to calculate a simple calculation from fields that already exist in a given table: ({gross_expected_revenue_per_qty_sold} * ({your_split} / 100) * {qty_sold}) - {cogs} - ({printing_costs} * {qty_sold})I thought I was going crazy, had two different AIs look over this, and even after that, it the formula doesn't work. None of the fields from which this calculation is pulling are empty, 0, or negative, and for some reason, this still throws an error when I run it.  By the way, your_split is a switch formula that puts out a percentage like "35%" for example. Thanks.Any idea what could be going wrong, here? Thanks!

Hello,I need your help with comments. Users of my interface can easily enter comments on a records. The problem is that I have other tools linked to Airtable, and I would like to find a way to put the date of the last comment or just the comment count into another field to trigger an automation that launches a webhook. Do you know if this is possible?Thanks in advance.

Hi There -Im having trouble with adding dynamic mentions with my slack message automation in airtable. Currently my airtable is connected to salesforce where it pulls a managers name into the airtable record. I'd like to set up an airtable automations that messages a specific slack channel, that mentions that specific manager. There will be a number of different managers and I dont have an actual list.From my understanding, I need to set up a formula that looks up the managers slack id, but im not sure how. I dont want to have to manually link the slack ID as there may be new users added from time to time. The airtable documentation around dynamic mentions kind of makes sense but im still missing the piece on how to lookup the slack id. It says to use the following CONCATENATE("<@", {Slack member id lookup}, ">")but im not sure if i need to manually find the id and insert it above? or if this exact formula should automatically pull the slack id for me. is this even poss

Hi everyone,I have been struggling with comparing a date of when a record has been created to a date I am manually inputing into the formula. So basically I am trying to use the IS_AFTER function but have tried so many things and just everything is either coming up with error or false to all fields even though i know some of them are after the date i am specifying.I am trying to get all records that are true to being created after the 21/10/2024 and feed them into a make.com automation using this field or formula.This is the formula I have been using but all the fields are showing up as 0 (false) when there are definitely some records created after the 21/10/2024. I had to use the date format with the date parse as it was automatically formatting the date in US format even though I have specified the format? IS_AFTER(DATETIME_FORMAT(Created, "DD/MM/YYYY hh:mma"), DATETIME_FORMAT(DATETIME_PARSE("21/10/2024 00:01am", "DD/MM/YYYY hh:mma"), "DD/MM/YYYY hh:mma")) I also tried form

Hi there,I'm trying to utilize Airtable to manage Shopify Metafields (among other things), but am striking out with doing this in what I believe to be the most logical way.I've configured a field (column) as "multiple select" with preconfigured options (the array). This ensures we're dealing with legitimate values.Syncing to Shopify, through the use of AirPower, requires the metafields to be in the format:["MF1","MF2","MF3"] I've used ARRAYJOIN to convert the array to a string in a separate field, but am stumped at actually formatting the square brackets and quotes.Suggestions?TIA,Jeff

Hey there. I’m using this formula for a column: DATETIME_FORMAT({Date of Donation}, ‘M/D/YYYY’) & " — " & IF({Donor Account}, {Donor Account},{Company account}) It’s pulling some company account names that have commas in the title, generating unwanted quotation marks around the field name.: 10/24/2019 — “Farrar, Straus & Giroux” Can I drop something into the formula that will eliminate the quotes?

Hello, I need help structuring my datetime_format as it has been showing inaccurate days.What I want is to have ~ Live Event ~ On-Air Start date(MMDDYY) and my formula is below:However, with this formula, it is producing a date that is the following day when any time is past 12:00. I'm using the 24 hour time clock.Can someone help me fix this? 

Hello! I am currently using the below formula to pull together a list of cities that correspond to a record. I have City 1, City 2, City 3, City 4, City 5, Cities 6 fields. Formula looks like this: CONCATENATE({BP City 1}, ", ",{BP City 2}, ", ",{BP City 3}, ", ",{BP City 4}, ", ",{BP City 5}, ", ",{BP Cities 6}) My formula works if all 6 fields contain cities although it doesn’t look great if only one city is listed. It ends up looking like this: Philadelphia, , , , , Anyone know how to fix the formula so that the appropriate number of commas appear so there will not be a string of messy commas at the end? Thank you!

Hello, and first off, thank you to everyone for all the help in the community. Long time lurker, first time poster! My business uses a series of documents that go through a few statuses - drafted - sent - viewed - and complete. The way my table is set up and with a few little automations I have the ability to know the duration between those statuses. The problem is, I only can get the duration between the statuses when they change, not the duration that’s been going on between that time - if that makes sense. My end goal is to know (even roughly) how much time (days are fine) since a last update of any of the actions. For example, I would like to know how long it’s been since ‘viewed’ so I can see if a document is past the parameters of being on-time and contact my customer to support them on completing their document. More simply - I would like to know how long something is ‘in’ one of the statuses - again, rough dates are totally ok. Initially I tried using a today() formula, but not

I'm attempting to assign an Award Name to an Award Picked field if an applicant says 'Yes" they want to apply for that award.So, if someone says 'yes' to the First Award, I would like the Award Picked column to read "First Award Name".  Same for Second, Third, etc. (I have 21 awards). If they don't say yes, then it should be 'blank'.I tried the AI formula (which came up with the same thing I tried myself) but the Award Name does not appear, only 'blank'.Any help would be much appreciated!! 

Hi Community, i have a single select field with the following options. I want to add up the results from different columns in a formula. To do this, I need to extract the score from the rest. For example, only “2” should be extracted from “2 More then 20%” in the formula.What is the best way to do this?   Thanks & bestMirko 

Hello,I'd like to compare the date between two columns from the same table.I've used the chart extension but I can't seem to add it on my interface. Here is how the data looks like : Is it even possible to compare it on the interface? Or add the chart extension on the interface?Would like some help please.Thank you

I am facing difficulty in prefilling forms with 'Link to another record data type'Data is occasionally getting printed when the data type is 'Link to another record' with 'Allow linking to multiple records' switched 'ON' What changes should I make so that it works well? 

Hello everyone,I am trying to get my formula to recognize numbers >= $0.00 in all of the columns listed:  My formula: IF({August FY25 Projections},{August FY25 Projections},IF({FY Start Split Formula},{FY Start Split Formula},{Original FY25 Revenue Projections})) For reference:The August FY25 Projections column is a currency fieldThe other 2 fields are formulated fields with a currency format.Attached you can see the "Updated FY25 Revenue Projections" column is skipping over the $0.00 amount that is listed in the August field. This is incorrect and should have picked up the $0.00 amount as per the order of the IF statement. Any help would be appreciated! 

Hi all,I have two tables: Projects and Tasks. Each task has one or more municipalities. What I'd like to do is check if all the municipalities in each task associated with a particular project are the same. I initially thought I could do this with a rollup and ARRAYUNIQUE(values) and a comparison of the Municipality field with the rolled up field. As you can see below "Glenville, Rotterdam" is a match, but that is not what I am looking for. I only want a match if all the fields are exactly the same.Any ideas on how to achieve this? 

Brand new to Airtable, so forgive me if this is a dumb question.  I've shamelessly stolen a formula to calculate start and end dates based off a T- or T+ from a specific event. DATETIME_FORMAT(DATEADD({Calculated End date},{Days Needed to Complete},'days'),'MM/DD/YY') The dates are calculating just fine, but when I try to add a Gantt view, it doesn't allow me to choose these fields to determine the dates for the Gantt.  What do I need to do to have Airtable recognize the output of this formula as a date?