Formulas

I am no geek when it comes to sheets, most of the stuff I do comes from watching a video on youtube. I have a retail booth at a flea market and I get  my items from various resources, Some items come from estates that I buy, some from garage sales, some from a local auction house etc. I use the cents in the pricing to help identify where the items were purchased. For an estate sale, the price would be, for example, 10.31, so I can look at the daily sales report and immediately identify every item sold ending in .31came from the estate sale. What I’m wanting to do is  list the different sources I use and have sheets track and total each item based up the cents.  So in the Cell that has a heading Estate Sales, create a sum formula with the criteria of searching the sales report looking for every item sold that ended .31 Thanks in advance for the replies.  If this isn’t possible, is there a way to copy and paste selected random cells.  When I highlight multiple cells using the ctrl tab

Hello! I am struggling to create an easy database template for importing data from a manual CSV export with some weirdness to it. Then an interface for data visualization that once was done using a platform that linked directly with the app generating the data. The app generating the data exports two CSV files, one for a list of “interactions” and one for a list of “buttons”, so I started with a table for each with the same fields. Each interaction row contains a lot of data pertaining to the user, so I added a 3rd table of Users in Airtable, and just made those fields in the Interactions table lookup fields to pull from Users. The Interactions can have up to 12 Buttons, and the export has several columns for each button - some of it is unique to the interaction and some applies to every use of that button, so I have put that info in the Button table and the fields in Interactions are now a lookup. The Interactions records also include date and time of the start of the i

Hi,One table has the entire list of our shows with an ad budget attached to each. Another table has an entire list of each ad placed, but our ads include multiple shows in each one so there’s already a linked record there, then a formula to calculate total ad spend versus how many shows are in that one ad so that we know how much is spent on each show per ad.Is there a way to use a rollup field or formula field to add up our ad spend per show and attach it to our show list in the first table? I need it to calculate correctly for each show, not just spit out the total amount.  

HIIMAGINE A RECORD…..PRIME = DRAWING 1234, MATERIAL = ABCD, LEVEL = 1 AND QTY = 5.NOW IMAGINE ANOTHER RECORD - SAME DRAWING AND MATERIAL, LEVEL = 2 AND 10 = 10 I AM USING QTY TO DO A TOTAL IN ANOTHER TABLE - AND IN THIS CASE IF I DID THAT I WOULD GET 15….WHAT I NEED TO DO IS TO FIGURE OUT A FORMULA, AUTOMATION OR ROLLUP ETC TO ACTUALLY SAY THAT I WANT THE THE QTY FROM ANY RECORD THAT HAS SAME DRAWING AND MATERIAL BUT ONLY THE HIGHEST LEVEL…..IN THIS CASE THE 10 ON LEVEL 2….AND THE 5 SHOULD DROP TO 0 (USING A NEW QTY DATAFIELD THAN THE ORIGINAL QTY).IF ANOTHER RECORD COMES IN WITH SAME DRAWING AND MATERIAL BUT NOW LEVEL 3 QTY 20….THEN THE RECORDS WITH 5 AND 10 SHOULD BOTH DROP TO 0….AND I COUNT 20 FOR ALL 3 RECORDS IN THE ROLLUP...AND SO ON….. LOOKED AT ARRAY FUNCTIONS BUT DO NOT SEE ANYTHING THERE I COULD USE AND CREATING A LOOKUP LOOPING ON ITSELF DID NOT SEEM TO GIVE ME A BASIS FOR GROUPING THE 3 RECORDS BUT SEEING COUNTS OF 0, 0, 2

I’ve tried many different ways to set up click to dial with ringcentral app, and it even worked the first time I set it up, but for some reason, its not working. I’ve tried creating formulas that others have suggested, buttons, etc… but no mater what I do, I always get this error in the address bar of the new tab that opens: about:blank#blockedIf I manually type tel:+16199309785 it dial no problem, excel the link i just typed even works but it won’t click to dial from the grid in airtable. I’ve tried chrome and edge, same thing. Any suggestions?

Hey all, I’ve got another (hopefully) easy question for y’all.IF({Rental Fee}="", "", {Rental Fee} / {Open Duration})Here’s what I’ve got. Open Duration is another equation where I used DATETIME_DIFF to calculate the number of months (success!). Rental Fee is a currency field where I plug in the numbers. What I really want to do is see how much the rental fee came out to per month.Cool thing is that the IF field is working well enough, I only get #ERROR! on the fields where the equation is applicable. Not cool thing is the whole #ERROR! message bit. 😂 If rental fee is $10,000 and duration is 10, then why am I not getting $1,000?I can’t figure out what I’ve done wrong. Any thoughts? Thank you in advance!

I’ve been trying to make formula to proper clean person name, but the result is weird. It’s like the LOWER() function dominates the whole result even though I put it in the middle… is this a bug?if I remove the LOWER() it only works if you dont meet with the all capital names (Like “JOHN DOE WHITE” would stay the same)I even tinker with internal AI and Copilot with the same results.I basically just want to make start with Capital, clean all unnecesarry whitespacesCan somebody help me?   

Hello,I am building a dashboard in which I include a control formula in order to make sure the AI reads well.However the behaviour is inconsistent. Check formula :IF({Amount (vat incl.)}={Amount (VAT incl.) (check)},"✅","❌") Calcul formula{Amount (vat incl.)}-{Amount (VAT incl.) (check)} QUESTIONWhy is one result -0 and the other 0 when the mechanics between formulas and parameters are the same ?  

I’ve got a very simple IF statement Facon is a check box, and it’s clearly empty.Material Supplier address is a lookup and is filled in in most instances. However, I can’t get this basic thing to work? Am I doing something wrong?

I’m trying to write a nested if formula in a CRM document. I have a column for the frequency I need to contact someone, a column with the date they were last contacted and a column that I want to say “Overdue” if it’s more than X days since my last contact. For instance, if I have the Frequency marked Monthly, I want it to say “Overdue” if it’s been more than 30 days since my last contact.Here’s what I have now, but it doesn’t say “Overdue” when it should. Everything in the column is blank.IF(AND(Frequency="Weekly",DATETIME_DIFF({Last Contact}

I was told Airtables had better database like features than Notion, but cant find them I have 3 tables, A → B → C , and want to get data from A in C but cant find a wayFor example I have a clients table, a contracts table and a monthly payments table, in payments table i link to the contract to be paid (and ofc contracts links to clients), clients table include a field security_deposit. I want to calculate the amount to pay for the given month, and include a checkbox to mark its the first payment. Another thing (that Notion can actually do) is to reference a column of a linked table in a formula, I cant find away to reference data by formula in Airtables.For example, in Notion using gui i cant do what i want, but I can use a formula to achieve it.This would be something like: if( first, contract.map(c

Hi,We’re trying to analyse operational and financial data longitudinally, over time, where e.g. weekly sales averages would be shown in a table in an interface.I know I can have averages with Numbers in a dashboard, but it’s not possible to calculate averages in formulas (like in Excel, one can sum vertically across a certain field). This would e.g. allow us a rolling 6 month sales average.Another need is to be able to create projections, based on historical data. For this one would need the earlier mentioned longitudinal averages, and then have some coefficients that one can change and make scenarios. Any ideas how to achieve that?Multiple columns each consolidating data for a certain week? That becomes difficult since neither weeks or month can be defined as “after/before” a certain number, only the previous and the next are available directly.rgds,Björn

Hello, I inherited an Airtable base that essential keeps track of new subscribers. It was set up to calculate the enrollment date (ex: 7/24/2025) and extrapolate the year registered into a new column. However, currently the column mostly remains blank and/or it has enrolled everyone in the year 2022. This information is then supposed to be reflected on another table with their information. I am new to using Airtable and any information or direct you can provide is greatly appreciated on how to input an effective dynamic date formula. 

Hello there! I need some assistance understanding why these return different results. DATETIME_DIFF(“8/18/2025”, TODAY(), “m”) = 0MONTH(“8/18/2025”) - MONTH(TODAY()) = 1 Thanks in advance.-K

I have a name field that sometimes has one name and sometimes has two. If there are two names, they are seperated by a comma.What is a formula I can use to show only the first names, with an ‘and’ in the middle if there are two.John Doe                                    JohnJohn Doe, Josh Frank             John and Josh

Hi, hi. Happy Midsummer. I hope you are all wonderful. I have the most simple task that I cannot figure out for the past ten days. I'll attach an image here. I set up my inventory tracking system. I included a button to receive packages of inventory and a button to delete used packages of inventory. I cannot connect these buttons to update my current inventory column. My buttons open beautifully to the form, but when I input two boxes were received, nothing is updated in my current inventory. When I update two boxes were used, nothing updates as well.Let me know what additional information I can give you if you have a possible code. I've tried a bunch, and it seems like such an easy task, but nothing is sinking on my end for me to make it usable.Thank you. Thank you for your help. I appreciate you so much.Lisa  

Hi, I want to track employee annual leave via Airtable. They’ll input Date From & Date To (including time). There will be individual calculation fields for each leave type (e.g sick, annual leave etc) and an approval status. So this formula would be wrapped in IF conditions; IF Type = ‘Annual Leave” and Status = “Approved” this calculation will apply. Formula needs toonly calculate weekdays (ignore weekends) understand 1 day = 7.8 hours only needs to count hours if the time differs between date from & date to fields. Understand working hours are 8:30-5pm (time zones differ) So outputs for the following would be;17 June 2025 3:00pm → 17 June 2025 5:00pm → 2.0​​​​​​​17 June 2025 3:00pm → 18 June 2025 3:00pm → 7.8​​​​​​​17 June 2025 3:00pm → 18 June 2025 5:00pm → 9.8​​​​​​​17 June 2025 3:00pm → 24 June 2025 3:00pm → 46.8 I’m open to d

Hello! Recently took over Airtable and am getting familiar with it. I set up an easy lookup between linked tables, but am having difficulty with a little more complex scenario. I have two tables that are linked, let’s call them ‘Operations’ and ‘Account Management’.Each table has fields that are: Partner, Territory and Business Line.I’m trying to set up a lookup in the Operations Table, where if all three of the values match in the linked tables (match on Partner, Territory & Business Line) - then return the value in the ‘Account Manager’ field which is stored in the Account Management table. Assuming this is possible, any ideas on the easiest way?

Hi useful people, Our Airtable database contains multiple services with attendance data. We also log the individual names of each attendee. Airtable can easily gather the total about of attendees across a period of time - my question is, how does Airtable gather the individual data? For example, Airtable may tell us that there were 1000 attendees across a calendar year, but how many many of those are the same people? What we’re looking for is the amount of individuals who have accessed the service in the calendar year, not a total attendance tally. I hope that makes sense… thank you in advance.

I am new to airtable and am having trouble.  I have a cattle ranch and am working on a database to manage their reproduction.  I would like to calculate each cows conception rate based on the number of times she has become pregnant vervus the total number of opportunities she has mated.  For example, if she has been with a bull 10 times and has become pregnant 7 times in her lifetime, then her conception rate is 70%.I have each cows breeding records in the data.  I also have a field titled “pregnancy status.” If i use rollup for this field with:Source= Cattle ID#Cattle field to rollup= Pregnancy StatusPregnancy status field is: pregnant or non-pregnantHow would I create a formula that would look up the pregnancy status for each mating and then calculate the percentage pregnant for the cow’s lifetime? Thank you so much for any help you can give me!  

Hello, I have a JF table which contains dates of public holidays and an APPRO table which contains a date field. I can't test if the date field in the APPRO table is a public holiday. Does anyone have a solution?

I have a date time difference formula formatted to decimal with 2 places but not returning any values less that one. My formula is as follows: DATETIME_DIFF({End Time},{Start Time},'hours')What am I missing? If I recall correctly this should be able to return hours less than 1 (e.g. 0.5 for half an hour difference)

bonjour la communauté, la nouvelle mise à jour des champs de formule permet des sorties à sélection unique pour les champs de formule. J'ai un champs ou est concaténé plusieurs informations incluant une sélection unique. J'ai testé cette nouvelle fonctionnalité et apparaît la sélection unique en couleur, mais les autres informations concaténer n'apparaissent plus.merci de votre retour 😉

Hi, can someone please help with my formula? I’d like to calculate the number of days between two dates, but if the dates are the same then they should display 0. The formula below works to show the ones with the same date as 0, however it then shows the ones with different dates as being one figure too little. DATETIME_DIFF(  {Post Sale Date},  {Auction Date/Time (Local)},  'days') To fix this I added +1 to the formula, which of course, solves the latter issue but not the issue for same dates need to show 0. DATETIME_DIFF(  {Post Sale Date},  {Auction Date/Time (Local)},  'days') +1 Can someone please assist 😊

I'm working with a database structure that has three tables: Publications, Artworks, and Individuals. I'm running into an issue with a Rollup field and hoping someone can help me find a solution.My Table Structure:Artworks table: Has a field that references one or more Individuals Publications table: Has a field that references one or more Artworks Publications table: I want to create a Rollup field that shows all Individuals associated with the Artworks in each PublicationThe Problem:When I use a Rollup field with an ARRAYUNIQUE() formula to get the individuals associated with the artworks, I'm getting unexpected results.For example, if a Publication contains 10 Artworks, and each Artwork has 1, 2, or 3 associated Individuals (sometimes different ones), the Rollup field returns each combination