Formulas

Is there a programmatic way to count columns? I am aware that COUNTA() exists, but I have to manually input the column names into the formula. Is there a way I can access the names of the columns and count the ones that contain the word “Siege” for example?

I don’t quite understand why I am seeing two different kinds of behavior from very similar ARRAYUNIQUE rollup fields. I am making a database of health issues. One table is Substances (nutrients, herbs, medications, etc), another is Issues (diseases, problems, or symptoms), and then I have a Junction table where I can select a Substance and an Issue and add in some other information (how/why they’re connected, along with references). On the Issue table, I want to be able to look at the record for an Issue and see all the Substances associated with it. So I have a Rollup set to ARRAYUNIQUE the Substances it looks up, because while I may have multiple Junction table records connecting the same Issue to the same Substance, I only want to see the Substance listed once. (For example, I may enter in many references in the Junction table for carnitine correcting exercise intolerance, but when I look up the exercise intolerance record in order to see what may be helpful to someone, I only need

I’m trying to calculate dates based on two values. I have a formula that works for this: IF({Renewal type} = "Annual", DATETIME_FORMAT(DATEADD({Last register date}, 90, 'days'), "MM/DD/YYYY")) If I try to add another IF statement then it fails: IF({Renewal type} = "Annual", DATETIME_FORMAT(DATEADD({Last register date}, 90, 'days'), "MM/DD/YYYY"),IF({Renewal type} = "Semi Annual", DATETIME_FORMAT(DATEADD({Last register date}, 300, 'days'), "MM/DD/YYYY") What am I doing wrong?

Airtable doesn’t seem especially forgiving of formulas doing numerical operations when the original cell contained text. For example, I have a cell describing engine sizes of cars. They’re pasted in like the following: “1498cc”. I’d like to show the engine size in litres, which would translate to “1.5 Litres” What I’d like to do is have a formula substitute out the ‘cc’, then divide the remaining 1498 by 1000 (which would be 1.498), then round the 1.498 to 1 decimal place (1.5), and then put " Litres" at the end of it. The following formula works exactly as you’d expect in Google Sheets / Excel: =concatenate(round(substitute({Engine Size}, “cc”, “”)/1000, 1), " Litres") However, Airtable formulas doesn’t seem to be able to treat ‘1498’ as a number after the ‘cc’ has been removed.

Hi, When there is a field with a pure text value “1 + 1”, I would like to dynamically calculate it and put the result in to the other field. For example: | 1 + 1 | 2 | | 2 * 3 | 6 | Thank you.

Hi, whenever I try to reference a set of results I have in a “YIELD” column I get an error. the yield data is the result of a nested IF formula which itself references a single select field. Is there something I’m missing that means this data can’t be referenced by another formula? The Yield formula is: IF(TYPE = “Firkin”, “62”, IF(TYPE = “Pin”, “31”, IF(TYPE = “20L Keg”,“0”, IF(TYPE = “30L Keg”,“0”, IF(TYPE = “24 Case”,“24”, IF(TYPE = “20 Case”,“20”, IF(TYPE = “18 Case”,“18”, IF(TYPE = “12 Case”,“12”, IF(TYPE = “8 Case”,“8”, IF(TYPE = “6 Case”,“6”, “”)))))))))) Where TYPE is a single select field. But even if I try another formula field with YIELD*2 I get an error. Any help much appreciated!

Hi all – new to Airtable, but over the last 3 weeks have absolutely loved it and now using it for everything. It’s awesome. I need help with something that I’m not sure can be done yet. I have a CRM, with a table that is a funnel, each record being a different lead. I have a separate table that is my business dashboard: a summary of my activity. I want to have on my dashboard the number of leads I’ve contacted per month, based on the creation date of each record in the CRM Funnel. Is there a way to count all the records in a table, determine what their creation date is, and then get that single number and put it in a cell? Thanks in advance, Scott

This seems silly to me, but it appears that any percentage type field used in a calculated field is treated as a number instead of a percentage. For example: A percentage field set at 20% A currency Field set at $500 multiplied together = $10,000 Change the 20% to 0.20% and then it equals $100. Maybe this is intentional, but I cant’ figure out why.

Hello! Using the current formula: IF(DATETIME_FORMAT({Date of Outreach}, ‘DD/MM/YYYY’) = 22/07/2017, “ :white_check_mark: ”, “ :x: ”) But for some reason it doesn’t validate when the date is 22/07/2017 in Date of Outreach field… Any ideas why?

Help! How do I combine first name (column 1) and last names (column 2) in a 3rd column?

I have created a long list of concert dates in AirTable. I group the list based on artist name, and sort by concert date, so each concert date for a given artist appears chronologically in one data set. For an integration with another system, I need the last date listed for an artist to have a check box checked in the same row as that final concert date. Keep in mind also that as concert dates are announced, they get added to the list, at which point the check box for the previous last date must be unchecked, and the new final date check box should be checked. My process now is that I manually review the list and uncheck the previous last concert date, and check the updated last concert date. Any thoughts on automating this process?

Any time I have a field that is a formula then any IF function that bases it’s action off the number returned by the first formula is an error. I’ve tried adding the value tag around it… still not working. It would be something like, IF ({number column}<1, ‘0’, ‘1’) which will work, but If that formula is recorded under ‘{column 2}’ and I then say, IF’{column 2} = ‘1’, ‘yes’, ‘no’) will always be an error. How can I fix this??

So I’m using a formula in a column to pull data for another table. This is my formula: IF({Test Type} = “CAT 1”, {Date Only}) Which is basically: If the test type is CAT 1, then display the date from the ‘date only’ column. My problem is this… sometimes tests are both CAT 1 AND CAT 5, but I still need it to pull the data. So what I really need is IF test type CONTAINS CAT 1, then display the date from the ‘date only’ column. I used Fieldbook before Airtable and in that instance I was told to use a formula like this: IF({Test Type}: “CAT 1”, {Date Only}) In this case, : was used to signify CONTAINS. Is there something like that in Airtable? Thanks!!

Hi. Is it possible to set a conditional formatting with 3 results? If the result of 80 or more, the cell shows “Go” If the result is lower than 80, but higer than 50, the cell shows “Caution” If the result is lower than 50, the cell shows “Run”. Thanks

Hi! For some Uncide codepoints that are encoded as 4-byte UTF-8, such as 🌸, 🎧 and 𐄁, Airtable’s LEN formula function appears to return 2 instead of the expected 1. This is not the case for 2-byte or 3-byte UTF-8 encoded characters as far as I can tell, such as ⛄ or Đ, for which LEN will correctly return 1. I’ve created a sample Airtable here that highlights the problem. I discovered this when using LEN("⭐️⭐️⭐️⭐️⭐️") as an answer to this post where it returns 10 instead of the expected 5. To find characters to test with I used the UTF-8 ranges to generate some code-points with a local Ruby IRB prompt: E.g. "\u{0110}".bytesize will give you Đ as a 3-byte UTF-8 encoded character.

Multi-select fields containing a value with a " (double quote) in it yield unexpected results with formulas—essentially because Airtable seems to escape double quotes in arrays with another double quote rather than a backslash or something else. Here’s a series of formula results on a multi-select field named {multi} containing a single option with the value: 6" Bar Pull Formula: {multi} Result: “6"” Bar Pull" Formula: FIND('6" Bar Pull",{multi}) Result: 0 Formula: FIND(‘6" Bar Pull’, REPLACE(multi, FIND(’""’, multi), 2, ‘"’)) Result: 2

How do I reference a second table in a formula? I need to sum all the values of a column in a second table and then divide it by a rollup of a records associated values in that column. What I am trying to do: I have an event where all the people attending are in one table. I have another table where I have all the receipts people submitted for things they had to pay for and there’s a link between the two. (IE, attendee x paid for receipts A, B and F) What I want to do for each attendee on the attendee table is sum all the values of the receipts, divide it by the number of attendees and and subtract the total receipts that attendee already paid for. That way I can get an average cost that each person needs to pay for their “share” and automatically subtract what they’ve already paid. Thanks for any help.

Hi, I am trying to replicate payout from server. The server calculates 34.9345 as 34.94 but on airtable it gets rounded off as 34.93 What should I do to make sure it gives the same value 34.94? Thanks in advance Regards, Gokul

Hi, Trying to add Minutes to a DateTime seems not to work when the minutes comes from another column. Col1 : DateTime Col2: Duration in Decimal (ie: 3.5 for 3h30m) Col3: DateAdd(Col1,Col2*60,‘minutes’) Result: Col3 = Col1 If instead of using Col2 in the second parameter, I use a constant value (ie:210), then it works. If I create a Col4 as a formula of Col2*60 and then use Col4 as the second parameter of DateAdd, then it’s also not working. Am I doing something wrong? Cheers, Berni

For us newbies, is there a place where users are posting tips and techniques? For example, I finally came up with a formula to determine someone’s age if you have their date of birth. DATETIME_DIFF({D.O.B.}, TODAY(), “days”) / (-365) Anyway, just thinking that there are a lot of experiences users who might want to share their own tips.