Formulas

I have an orders table that records all of the orders we get. Each line item on the order gets it’s own record on the table. I have a field in that table that is order ID which is the same order ID for each line item in an order. I’m trying to get a count of the number of times an order ID appears. So if a customer orders three items I’ll have this field return “3” so that I know there are three items on this order. I can’t do a rollup or a count since order ID isn’t a linked field. Basically I just need a count of how many times a value repeats in a field.

I would like to know how to Count records from different table with specific conditions? For example I have a table called “Stats” where I would Like to put some spesific information (automatically) for example number of boys and girls (based on another table called Students with a field called sexe) ?

Hi! I have a rollup field (using ARRAYUNIQUE) that should always produce a single array value. I want to pretty it up and remove the encompassing quotation marks, but I can’t seem to figure it out. I’ve tried all of the following formulas, but none of them seem to work. SUBSTITUTE({Vendor Rollup}, '"', '') SUBSTITUTE({Vendor Rollup}, '/"', '') SUBSTITUTE({Vendor Rollup}, """, "") SUBSTITUTE({Vendor Rollup}, "/"", "") Can anyone help me shed some light on my issue?

Hi, We have a base that, amongst other things, generates requests and records when the request is completed. To provide feedback on performance, we measure the number of working days it took to complete the task using: WORKDAY_DIFF({Request Date},{Time Processed}) We can then use a Summary Block to give an average “headline" of the number of working days taken to complete. We then have then used the following to report the average during the previous month: IF(DATETIME_FORMAT({Request Date}, “YYYY-MM”) = DATETIME_FORMAT(DATEADD(NOW(), -1, “month”), “YYYY-MM”),WORKDAY_DIFF({Request Date},{Time Processed}),BLANK()) So here, during Aug, it will give the average for July. How can we have this a little more “live” by reporting the number of days taken to complete over the past 30 days?

Months Active is a formula that returns what appears to be a number. IF({First Gift Date}, DATETIME_DIFF({Last Gift Date},{First Gift Date},‘month’),"") Number of Gifts is a count field I’m trying to create a field that returns an frequency {Months Active}/{Number of Gifts} But it is throwing an #Error What am I over looking? I’m pretty sure it has something to do with the DATETIME_DIFF returning a date and not a number

Hi All, I am trying to build out an automated-ish roadmap and resource planning base that will help schedule features based on sprint data and resource availability. I used this base as a starting point: I started with this resource planning base: Resource Planning - Airtable Universe Here is the google sheet that I currently use to auto-schedule our roadmap: Roadmap Planning Example - Google Sheets The sheet doesn’t account for headcount, but it is easy to eyeball and pencil in. I’d like to use Airtable to autoschedule the roadmap based on feature complexity and then layer in the resource allocation to match. The problem that I have is Auto-scheduling fe

Hi, I’m new-ish and can’t figure out what seems like it should be a relatively simple problem. I am using a table to track attendance in a 10-week training program. I created a column for each week/day with a drop-down of selections for our trainer to select from when taking attendance. There are corresponding hidden columns for each day with formulas that convert the drop-down selections to codes used to populate daily attendance records for each individual trainee (in a Page block). I need to create an at-a-glance attendance summary record for the whole class that sums up excused absences, unexcused absences, and partial absences, per person. I started by trying to create a column that would count all the instances of Excused Absences per record using a formula. I have tried the FIND(“Excused”, W#D#) formula - which works if I’m only looking at one field. But I can’t figure out how to expand the range being searched so it looks across all the days/columns. I t

I want to recreate a table in google sheets with Airtable but each row has a different formula and in Airtable you can’t assign different formulas for rows and cells. What are some workarounds if I want to calculate assign different formulas to cell A1, A2, A3 and then calculate e.g. A3-A1?

Trying to automate adding/remove users from systems. Working fine… except I can’t understand how to get a “forever” value to return “KeepUser” while using this formula. IF(IS_AFTER({Time math}, {Now}),“KeepUser”, “RemoveUser”) My select values are all numbers, except “forever”. I’d like to keep it as a text option, but I’m not sure how to get around this in a formula?

Hi, I am a new user… can someone help how to get the amount of the time duration? Thanks,

Hello, Sorry for bad English. I created a payment tracker, which allow me to track every rent from tenants. You can find the sheet here (translate in English). Usually, rents are grouped by tenants, but for the translation I do not have copy them. When I receive a payment, I write on the record the date of receipt, the means of payment and I check the “Done” field. To quickly see what rent I need to claim for the month, I am looking for a formula that shows “LATE PAYMENT” in red for every rent that I do not receive when the “Due” date is over. Exemple : Every rent for September must be paid at least on 5th September. If a tenant do not paid the rent on 6th September, the formula field in “Rent of September” will be shows “LATE PAYMENT” in red. Can you please tell me what formula I need to write to make this happen ? Than

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 w

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

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)” f

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?