-
Calculate Overall Status from Multiple Columns
Hello, I have a sheet with statuses in multiple columns, and I'm calculating overall status by using CONTAINS looking at the columns. This works just fine, except for one complication. When one or more columns is Complete, and one or more columns is Not Started, I need to show the overall status as In Progress. Any ideas…
-
Combining IF and OR
What I'm trying to say is: If A1 Rank = 1, 2 or 3 then Awareness If D Rank = 1, 2 or 3 then Desire If K Rank = 1, 2 or 3 then Knowledge If A2 Rank = 1, 2 or 3 then Ability If R Rank = 1, 2 or 3 then Reinforcement If none is true then NONE =IF(OR([A1 Rank]@row = “1”, [A1 Rank]@row = “2”, [A1 Rank]@row = “3”), “Awareness”,…
-
Calculate formulas by Columns
I am relatively new to Smartsheet and am stumped on a formula, and so are my coworkers. I do QA for multiple teams and I need to be able to calculate information such as # of Exceptional scores (100%), Meets Expectations (85% - 99%), and Needs Improvement (0 - 84%). We tried Countifs, Averageif, and a few others and did…
-
Copy a new text entry to another sheet without duplication
Hi Smartsheet Community, Dummy scenario is: I am using a smartsheet form to survey people on what their favourite colour is? Since any responder can enter any colour or shade, I don't have the colours pre-defined. The survey captures the responses into a database, from which I will do my analysis and dashboard. How can I…
-
Copy row for all columns before "Yes" Column
I am working on a project, and there will be MANY times that there will be multiple requests. This column is a dropdown with 2 options, "Yes" or "No". If the response is "Yes" I would like the data from that row, all columns PRIOR to that column copied to the next row. If the dropdown selection is "No" nothing is done.…
-
Project Status Formula to return blank
Hello, I am looking to return a blank cell in my parent lines in my project status column if there is no project status listed. The formula I have right now is: =IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started",…
-
Counting the number of items due in the next six months from today's date
Hi, I am struggling to find a formula that can count the number of items (each row represents a task/project) that are due in the next six months from TODAY (the date of viewing). At the moment the below formula is incorrect ... =COUNTIF([Target Closure Date]:[Target Closure Date], IFERROR(MONTH(@cell), TODAY(MONTH(+6))))…
-
Suming Amounts if numbers match
=SUMIFS([Revenue Projection]25:[Revenue Projection]36, [Month - Count]25:[Month - Count]36, >=[Month - Count]7) I want to say sum revenue amounts if the months are greater than the current month (8). The problem I see is its only adding months 8 & 9 and not 10-12.
-
Matching values between column and row between sheets and returning corresponding value
First post. Struggling with this one. On Sheet 1, I want to compare the week number from a column on sheet 1, to the week number in a row (row1) on sheet 2 and then return the value, in this case a symbol from row 4 on sheet 2 . Sheet1 - Sheet 2 - Am only showing part of week number range. But in this case am I after - if…
-
Duplicate Values causing multiple emails being sent with the same info
So I have a sheet that is essentially tracking missed attendances. And we want to know when a miss happens each time. I have it set up currently to check for duplicates and then a box gets check for each miss up to 3. Then I have a workflow to notify different people based on the number of miss it was. My issue is that…
