-
Help with If/Then statement evaluating condition of equipment life
Hi, I have a sheet with [Asset Status], [End of Life Date] and I'm trying to get a {red,green,yellow,gray} button returned. My formula below works as long as there is a date in [End of life date]. I'm out of ideas why. =IF([Asset Status]@row <> "Active", "Gray", IF(AND([Asset Status]@row = "Active", [End of Life Date]@row…
-
Add Values Generated by a formula into a form
Hello, Is there a way to add values generated by a formula into a form? I've created a dynamic drop-down list for a list of addresses. I added a formula column that shows the appropriate city based on the address. I am trying to add the city value, which is generated by a formula, into a form. Is there a way to accomplish…
-
Sum Ifs in a date range
Trying to calculate the sum of Range 4 only in between the dates below. I'm going to be calculating by quarter, so if there is a better formula for Q1, Q2, etc., please let me know. =SUMIFS({Construction Range 4}, {Construction Range 5}, "Macy", {Construction Range 3}, >=DATE(2024, 1, 1), {Construction Range 3},…
-
Count the # of projects with end date 60 days from today
I want to count the number of "Active" projects with an End Date within 60 days of today. I tried this and got an invalid operations error. =COUNTIFS({sheetname range},"Active",{sheetname range} <=TODAY(60)) The first sheetname range is the Type column (shows Active, Completed, Planned, etc.) The second sheet name is the…
-
If then formula help
Hi Community! I have been trying to get a few if then formulas but, I'm having lots of issues.... Typing out the scenario and hoping that all of your brilliant people can help me figure this out... Have a big SS. Certain projects will need a work breakdown schedule with a start to finish planner. All projects need certain…
-
Is the column type "Modified Date" a date column?
Hi, I'm trying to get the most recent date from a "modified date" column that smartsheet fills automatically. Here is what I'm trying: =MAX(Updated:Updated) And I'm getting the error: #INVALID COLUMN VALUE Just for kicks, I changed the column type to a straight up "date" column and got the same error. Help?
-
Newbie Question - simple formula help
Hi all - I'm trying to create a formula to automatically divide a $amount by 4 IF the accrual column is listed as "all 2024". OR a more complicated formula: ideally id like to cross reference sheets to SUM all $amounts for Accrual: Q1 AND Accrual "All 2024/divided by 4" . Is it possible to do this? total $ requested for Q1…
-
Distinct collect formula error
Hi I am trying to calculate the value of live accounts from a total sales pipeline The total sales pipeline lets say is $10,569,815 the customers live value is only $292,200,00 but when I use the following formula : =SUM(DISTINCT(COLLECT(ARR14:ARR224, [Sales Stage]14:[Sales Stage]224, "live"))) it only calculates a total…
-
Formula to calculate % Complete based on amount of completed tasks vs total tasks
Hello Everyone, For a new project I am working on, i'd like to use a formula that calculates the % complete of a project based on the total amount of tasks, and the tasked marked as complete in the status column. In my sheet summary, I have a COUNT formula that has totaled all tasks, and all tasks marked as complete but I…
-
Getting error while indexing entries from other sheet.
Hello there! A little background: So I have 2 sheets that get inputs from two forms (Form 1 and Form 2). Both sheets belong to the same student(one gets filled by the college and one by the student). I am using the ID number as the identifier and On the backend of Form 2 I have used the index match formula to gather all…