-
Nested IF statement for multiple date range
Hello! I am currently attempting to write an all encompassing formula to read through different work windows. We are only able to work in certain areas of the city depending on the "work window". Depending on the work window, these dates have differing start and end dates. I can get one window to work properly, but the…
-
Average of the first 3 numbers and average of the 4th+ number
I'm trying to find the average of the first 3 months of data and then find the average of the 4th month and beyond of data. So for instance, the first row, the average of the red values and then the average of the green values. The start of data for every row could be different and the number total values of each row could…
-
Formula to assign a number based on criteria
Hi All, I am looking for some advice here. I work with patients who are being scanned for research. Each scan takes place at an imaging site. I have data that is automatically exported in via our API with our platform that records the orders for a scan. The sheet automatically copies rows based on date to a new sheet where…
-
Formatting currency with symblos
Hello, I am working on a smartsheet and need to know how to have to currency symbol automatically added based on the "Departure Country". I have two rows a Salary row and a Departure Country row. I need the Salary row to have the correct currency symbol based on the departure country which can be any country. PLease help…
-
Convert date format to "YYYY.MM.DD" in formula that combines date and text
Is there a way to do something like the excel formula =TEXT(TODAY(),"YYYY.MM.DD") in SmartSheet to allow a specific date format to appear in the output of a formula, regardless of the user's regional settings? All of our date columns have been set to the YYYY.MM.DD format, but we have formula columns that combine dates…
-
Formula Help
I need a formula that pulls the data from the column if that cell is not empty. (Ex; If 1 is empty but 2 is filled, pull data from field 2). As-Built Production Yield Final Production Yield 90% Production Yield 60% Production Yield 30% Production Yield Total Production Yield Target My current formula is below but it does…
-
IF formula returns unexpected TRUE result for text cells that start with the < or > character
I have text column in one of my sheets named EN Display Name. That column cannot contain duplicate values, so I added the formula below to a helper column in the sheet to make it more obvious when multiple rows had the same value in the EN Display Name column. =IF(COUNTIF([EN Display Name]:[EN Display Name], [EN Display…
-
Help to avoid hundreds of conditional formatting
Hi all, I have a sheet with columns that list a particular service. My rows are site names and we're tracking the level of adoption for each service with the below list: FI (H) FI (NR-H) FI (NI) C-FI P NI V N/A I want to have each one colour coded like you would with conditional formatting but I have 28 columns and so if I…
-
COUNTIF Formula with 2 Criteria - Status & Checkbox
Hi, I am trying to create a summary sheet from a detailed project schedule and would to create a COUNTIF formula that adds to the countif sum if the row status column is "complete" and the internal milestone column is checked; however, I am receiving an #UNPARSEABLE error and not sure what the issue is. Here is my formula:…
-
Trouble with join/collect or index/match, with multiple columns and multiple results
Hello, I can't seem to get this right and after too many hours on this, I am escalating it to the formula masters of this great community once again. I am attempting to create a cell that will collect and join all the matched criteria from two different columns with the same criteria. There are multiple matches in both…
