-
Count function
Hi I need help with a formula. I have a column that I want to count but only based on a values in a different column. I have tried the following formula but it's not working and I can't figure out what I am doing wrong. =countif([Test Script #]:[Test Script #], [Pass ?]:[Pass ?], <> "N/A") The Test Script # column is the…
-
Formula to collect the first and last values from children rows, but only if distinct.
Hello Community People, I'm working to create a report that will show a summary of the location where a person will be each day. The source sheet is set up with parent rows listing the date and child rows showing all the events scheduled for that date, and there is a single drop-down "Location" column to select the city in…
-
Sum range where relevant data may need to be first extracted from the cell
I have a series of columns where users enter number of samples tested each month. They can indicate the number of batches in the same column using a delimeter. E.g., 4(2) means 4 samples were tested in 2 batches. We cannot add a separate column to indicate batches. I want to sum the total number of samples across the range…
-
Nested "if" formula
I'm trying to convert this excel formula to Smartsheet but can't find the right procedure. Keeps coming back as unparseable. =IF(J3=100%,G3,IF(J3<100,"")) J3 will always be filled in with a % and G3 is a dollar amount. Any help is appreciated. Craig
-
IF/AND/BLANK Formula Logic
I'm trying to write a formula to reflect a progress % (using the bar symbol) based on three fields, where one field, if filled in, counts as 25%, the second field, if filled in, counts as 25%, and the third field, if filled in, counts as 50% (to equate to 100%). I'm using =IF(AND(NOT(ISBLANK, but I'm receiving errors. I…
-
Automatically Create Parent Row From Child/Children
What will be imported is the white (2,3 & 5,6,7) rows. I want a parent (ex: orange rows 1&4) to be auto-created for any unique number in the "Reference" field. Any help would be appreciated! Thank you!
-
Auto-Populating Field Based On Multiple Selections
Hello, I currently have a Smartsheet form that allows users to select multiple options: Each option corresponds to a specific contact name: I need to come up with a formula that auto-populates every contact name in one row based on the choices the user selects on the form. I figured out a way to do so when they only select…
-
Conditionally Parsing Values from a Multi-select Cell
Hi! I am trying to create a column formula that pulls only multi-select values which contain the string "ML" from a multi-select column. Here is example output that I am looking for: In this case, "Templates Used" contains the full list, while "Mini Lesson Templates" should only include values from "Templates Used" which…
-
Adding one working day (no blanks)
Hi, I am trying to add a day onto a date generated from a previous date - However, it needs to be a working day and the cells with no previous date need to stay blank until the previous date is entered... i am currently using - =[Expected Final or QA Approval]@row + 1 - however the cells with no date in the previous cells…
-
Formula Assistance
I am attempting to do the following: If status is Complete or N/A = Green If start date is within 5 days or past due and status is not Complete or N/A = Red If start date is more than 5 days and status is not Complete or N/A = Yellow I have the following formula, but no luck. Any help is greatly appreciated.…