-
Countif a dropdown was selected before a certain date
In our project management process, we have a drop down column for an employee to note where a project is in the process, ending with "Completed". I want to do a count for how many of these projects were completed in our last fiscal year. I'm looking for a formula to say "if "completed" was selected before 7/1/2020, then…
-
IF/AND
Hello, There may be a more efficient way to solve this so please feel free to suggest an alternate way. I have a column in my sheet that collects the time/date a form is submitted using the "Created - Date" field option . I need to set up an automation that would be triggered by the time the form was received AND the day…
-
Find the Percentage of the Total
Hi! I have 4 columns that I have added together in the 5th, Total column (using =sum formula). Each of the 4 columns is a single dropdown select (values: 1-10). I want to translate the Total into a percent in the Percentage column, so I thought if I took the total value and divided by 40 (the max amount) in the % column, I…
-
Nested IF/AND Formula
I'm receiving an #UNPARSABLE error for the following formula: =IF(AND([Completion Status]@row="Completed", [Actual End Date]@row<=TODAY()), "1", IF(AND([Completion Status]@row="In Progress", [Actual End Date]@row> TODAY()), "2", IF(AND([Completion Status]@row="Not Started", [Actual End Date]@row> TODAY ()), "3", "4")))…
-
Using SUMIFS with YEAR and MONTH from single referenced column
Hi all, I'm trying to create sheet that will sum up all revenue for a year, by month. End result should look something like this: I'm using this formula and receiving an #INCORRECT ARGUMENT SET error: =SUMIFS({anrev}, {vehicl}, "Site", {Scale Date}, IFERROR(YEAR(@cell), 0) = 2020, IFERROR(MONTH(@cell), 0) = 1) (this is for…
-
IFERROR MONTH
#UNPARSEABLE error when adding the MONTH function. =COUNTIFS({Incoming Quality 1}, Quality@row, {Intake Sheet Range 1}, IFERROR(MONTH(@cell),0) = 4))
-
Is it possible to create uniquely identified numbers using level 1 parent tasks as a baseline?
Is it possible to pull the first three letters of a parent task, and then give it an unique identifying number xxx-9999? I have groups of tasks that I would like to create an ID for whenever there is a newly created line, each line would inherently be a child to parent task. For example a subtask of an "Engineering" group…
-
LEFT Function
I want to add a "0" to the left of any number with just 5 digits and just duplicate any number with "H" in front.
-
Querying my sheet
Hello, i have these two columns: ESTIMATION PER HOUR (number) RESOLUTION (numbers) I want to query the sum of all ESTKM2/H matching if RES=5 and divide it by the number of items matching this query. In the end i want to calculate the mean of estimation among all resolution 5 jobs. The result would be something like= the…
-
Getting an image approved - how to set Approval Status from multiple cells, ignoring blanks
Hello! I have an image production process sheet that at the moment requires 1-3 approvers per image. The images to be approved live in a cloud drive - they do not get attached to the Smartsheet. Basically, when there is a contact email in an Approver cell, I need a formula to check what the Image Approval Status is. Here…