Formulas

How do I write a formula to add total based on the colour of field/column towards the left? e.g in this table I want to add all payments labelled cash and sum in field 7.

I tried all kinds of ways, but I can’t get it to work. I want to combine a lookup field (containing multiple currency values) with a placeholder symbol (e.g. “;” ) so that I can then in the next step substitute this placeholder for a line break so that each currency value goes onto a new line in the same field. So for example, I have a lookup field {Item Amount 1} that contains the following three currency values: 100.00 150.00 100.50. When I use the formula ARRAYJOIN({Total Item Amount 1},";") the result is: 100;150;100.5; However, I want it to return 100.00;150.00;100.50; so that I can then use the substitute formula to substitute the “;” for “\n” which would result in: 100.00 150.00 100.50 I can’t use the Formatting tab because it’s not a currency anymore. I’ve tried all kinds of possible solutions, including trying to concatenate the lookup field first, but I can’t get it to work. This procedure does work for a lookup field with just text strings that I want to place on a new line

So, I have thirty columns of test result records in which some students did not submit work. In these cases, there is not a zero but a blank. This maintains an average in the average formula column which is unskewed by zeros from lack of submissions. However, I want to automate a running total of test results recorded (not the score, just the presence of a score being recorded), so that I can see who is falling behind in their submissions and it really only needs to total the number of populated fields to the left of it. I am a beginner and assume that this requires a formula of some sort. :slightly_smiling_face:

Hey so I have various different categories/tags linked together into a single string with a combination of rollups and a formula. Sometimes there is a blank value for the last “{Tipologie Rollup}” and then I get an additional comma at the end of the string. How do I add an if statement to say IF the last character of string = “,” remove it. If it doesn’t do nothing. My current formula which works. IF({Technologie Rollup} = BLANK(), “”, {Technologie Rollup} & “,”) & IF({Bisogni Rollup} = BLANK(), “”, {Bisogni Rollup} & “,”) & IF({Settori Rollup} = BLANK(), “”, {Settori Rollup} & “,”) & IF({Tipologie Rollup} = BLANK(), “”, {Tipologie Rollup}) Just need to add that cleanup if statement in the scenario there is no Tipologie Rollup data.

Hello - I’m looking to do a formula that counts the number of records between two specific dates. Using the count function, I see lots of options for dates where it’s within the last week, month, etc. but nothing for a specific date range.

Hello Community! I have been trying to figure out how to perform a double conditioned rollup (if that makes any sense). The example I think will explain better the situation: In this example there are three tables that are concerned Project, Provider and Concept Catalogue. On the Concept Catalogue table there are records which contain scope for each of the providers by project. Each record has linked fields to Project and Providers. Now, I can nest-group the Concept Catalogue table as shown in the image, into projects and then into Providers, which will allow me to see the totals by-proyect-by-provider. I would like to obtain those values somehow in another table so I can use them. If I perform a rollup (for any of the fields on Concept Catalogue) on the Projects table it will give me the totals of all of the scopes for that specific project but mixing providers. If I perform a rollup (for any of the fields on Concept Catalogue) on the Providers table it will give me the totals by pro

I am creating a base with two tables to determine the likelihood of which medical condition is most causing symptoms. In table 1 I have essentially a dashboard. List of possible conditions, percent likely, my symptom number, number of symptoms possible, list of all symptoms I selected, etc. In table 2 I have a long list of symptoms possible in all the conditions. I have a column linked to table 1 pulling the condition type information, a column linked to table 1 called “link to possible diagnosis”, and a column linked to table 1 called “Condition (from possible diagnosis)”. “Link to possible diagnosis” has all of the conditions listed that share that symptom. “Condition (from possible diagnosis)” is currently empty. What I want to do is have a checkbox column in table 2 labeled “Experienced?” where, when checked, would copy the conditions from “Link to possible diagnosis” field for that specific symptom in table 2, to the “Condition (from possible diagnosis)” field for that specific s

I got two table in a base, one is main table which show big categories The other table is record the small task of the big categories (each small task data have link to the big categories) Can the main table rollup the earliest due date of the small task of that big categories and show up the due date and title of that small task in a same block?

I want to get minimum value in a cell however I want to exclude 0.

Hi all I want a field to only display if this condition is met: {Session Status} is “ :sparkles: Public. Live on web.” (Session Status} links to the [Session Status] table. One of the records in there is named “ :sparkles: Public. Live on web.” When I write a formula for: FIND({Session Status}, “ :sparkles: Public. Live on web.”) I get 0 as a result. I am guessing FIND() isn’t reading that field because it is a linked record. Any workarounds come to mind? Thanks!

I want the start date to be the initialized date of the task plus 1 day. The end date should be the start date plus whatever number is entered in the duration field. I was trying to use IF plus DATEADD to first check if there was data in the initialized and duration fields, then proceed to auto fill the start and end field. Any help is appreciated!

I’ve got a staff list that also has columns for who their supervisor is, who reports to them, and who their teammates are. I need to count the number of times the staff member’s name appears anywhere on the table. Can you help me craft that formula?

Hi! This is probably super simple but I just can’t find how. I have a multiple select column with some 15 options. I want to get the summary of how many times each option has been selected in the whole columns, so something like: Option 1 = 3 times, Option 2 = 0 times, Option 3 = 6 times … Not sure if it requires a formula or something different. Thanks in advance! :slightly_smiling_face:

I’m trying to write a formula that will take the total sum from ‘Participant Growth’ column and divide it by the sum from the ‘Output 1’ column. The sum in Column A is 3 and the sum in column B is 4. I’m trying to write a formula for this…The formula I currently have is: SUM({Participant Growth})/SUM({Output 1}). But it is not returning 0.75. Am I thinking about this correctly or might I need to create a new view?

Hi, Sorry if this seems a simple issue, but I’ve got a table with a list of venues… some start with “the” (eg: The Royal Albert Hall). I want to be able to display the list of venues in proper name order - ie: Royal Albert Hall, without “the”… I’m thinking having the full name as one field, and a second, formula, field which removes THE if it’s the first word, but not if it’s in the middle of the name - eg: Her Majesty’s THEatre, or Auditorium at THE Excel Centre… Could anyone help please? Thanks!

I have a table of activities with start and end date columns and a column for the person assigned to the activity. There will be hundreds of activities but only one person should be assigned to each activity. How can I see if a person is assigned to more than one activity during the same time period?

Hello, I’m trying to generate two separate outcomes in the same field where one of our clients receives a discount that the remainder of our clients do not. We have what is called a 1/2 Day Session that is normally $600.00 for a four-hour session. One of our clients is only charged $500.00 for that 1/2 Day Session and I want to be able to see both in the same field if possible. Would this fall under a nested IF statement, and if so, how would I go about writing that formula? Thank you.

Hi, A really long story made short; I’m trying to get Airtable to detect duplicate names in two rollup fields. I have the following tables in my base Equipment Projecttype A Projecttype B Location In my Equipment-table I’m keeping track of routers we own. Different fields shows how many we own in total, where and for what projects they are currently installed and (this is the problem) how many we have left in stock. The Equipment-table rolls up on which projects the routers are installed. I have 2 types of projects, each in their own table (A and B) - meaning, I have 2 rollup fields. Then, the projects run at different locations. More projects (even the two different projects) can run on the same location. The Locations-table links to both the project-tables but not the Equipment-Table. I’m using an ARRAYUNIQUE in the rollups in the Equipment-table and that way I get to see on what locations the routers are installed. My issue starts when I want to count the number of the different t

What it says on the tin. Different authors make different amounts, I’d love to add this kind of conditional logic based on what we’re entering in the “numbers” field, and have those prices automatically calculated in the sheet. Ideally, I would also be able to hide the formula and have it entered in the currency field so I can later filter by author later and have it automatically populate the total at the bottom of the table, but I won’t get picky.

Sorry I struggle like crazy with formulas and know I post on here so often but Airtable is amazing and has revolutionized our reported!!! Cue me getting over-excited and moving everything into Airtable which is great 99% of the time and leaves me with my head in my hands other days. I was just wondering if anyone could help me I have a base for my family support workers, I have added a date created so I know when the referral came in and we have a linked table for interactions which is essentially every time we contact them so there are a lot of linked files in here. What I am trying to do is add a column to work out the date of the first interaction and then a second to work out the number of days between the referral coming in and the volunteer contacting them. Please can someone help before I literally start pulling my hair out

Hello, I’m new here but it’s look like, it’s the tool I need. I have an e-commerce site with a lot of product with a lot of turnover and all this products are in bundles of products. Easy no ? So I want to create a table with all my product and create an other for each bundle and add all the products I need in it. The goal is to create a list of SKU to import them in my ecommerce site with an other tool. For exemple in the bundle A I need a chair so I had chair A. If the chair A is not in stock I would like to automaticaly add the chair B in the bundle and delete chair A, Later if the chair A is in stock I would like to add automaticaly the chair A and delete the chair B, If the chair A and the chair B is available, I want only the chair A First and important question, Is it possible to do it ? Second question, How ? I need some lead or tutorial with information. I hope to be clear, if I can explain more I can Thank

Hello there, I would like to add up number like this : 142.10,42,21.20 The result I want is 205.30 I don’t know how to transform the cell to an array and add up each line of the array Thx for your help

There are 2 fields of type “Data”. What formula should be written for field 3 so that the difference in minutes between the fields ‘Date1’ and ‘Date2’ is calculated there?

Hi, I have two fields containing image attachements. I want to join them in one, but when i try to do this it is printed as text and url… any ideas?

I’m trying to achieve the following: If Invoice Status is ‘Paid’, then ‘Paid’ If Invoice Status is empty and the date in Payment Due is on or after today, then ‘Due’ If Invoice Status is empty and the date in Payment Due is before today, then ‘Overdue’ I have created this formula, which is not working (it does show ‘Paid’ but not ‘Due’ where applicable): IF({Invoice Status} = ‘Paid’, ‘Paid’, IF(AND({Invoice Status)} = BLANK()),IS_AFTER({Payment Due}, TODAY()), ‘Due’, IF(AND({Invoice Status} = BLANK()),IS_BEFORE({Payment Due}, TODAY()), ‘Overdue’))) Any help would be much appreciated!