-
Current year streak and rolling unbroken streak formula help
I have a dashboard metric that I could use help with. I have a Consistency Club to recognize associates that achieve their monthly sales activity metrics, measured by individual month. Meaning if they have achieved their metrics for Jan, Feb, Mar, Apr, May and June their current year consistency streak would be 6. If the…
-
Is there a formula to calculate the overall state of a specific group of projects in a metadata?
Hi Smartsheet Community, I am trying to calculate the total number of green, yellow and red projects I have on a sheet based on different project groupings. My goal is to be able to have a chart in the dashboard that would report on the overall state of a group of projects based on their location or strategic initiative.…
-
Formula regarding Checkbox Column
Hello Smartsheet community, I'm attempting to write a formula for a project plan type grid sheet. One of the columns is a checkbox to represent when the item is complete. I'd like to write a formula that counts all the checked activities to get management a % complete value based on how many boxes are checked and the…
-
Row ID (ignoring blank cells)
I am trying to create a sheet which identifies batches of 25 rows and creates a specific batch ID, before moving onto the next 25 rows. (Batch Column) I also need to formula to ignore rows with blanks entries. (Row ID) I hope somebody is able to help
-
Can you please help me figure out what is incorrect with this formula?
=IF([Actual % Complete]@row) = 100, ""Blue"", IF(ISBLANK([End Date]@row)), ""NS"", IF(TODAY() - ([End Date]@row) > 30), ""Red"", IF(TODAY() - [End Date]@row) <=30), ""Yellow"", IF(TODAY() - [End Date]@row) = 0, ""Green"")))))
-
SUMIFS Formula
I am attempting to get the sum of two separate columns Number of Participants Reached (Adults) + Number of Participants Reached (Students) for FY 2021 only All 3 sets of numbers are in different columns I can get the total sum of the two columns right for all years but I can't figure out how to have it only calculate for…
-
Flag is others are flagged
I have an "At Risk" column and I want one row to flag if any in a set range of others are flagged. For example, on a project template, I have a "Planning Milestone"; I want the milestone to flag if any rows within the "Planning" phase are flagged. I was attempting to use a formula like this: =IF([At Risk]95:[At Risk]98 =…
-
Compare 2 Multi Select Drop Down Columns
I have 3 columns, all have the same multi select drop down options. I want the first column to be my selection, the second column is more of a checklist of what's complete, and the third column tells me what is left to be completed from the first column. Any idea if this can be done? Tried using collect formula.
-
How do I average children?
Please help! I am trying to come up with a formula to put in the parent row to get the average % completionof the children row. My children row % percentage completion are filled in using this formula: =(IF(Pulled@row, 0.5, 0) + (IF([Source Termed (FROM)]@row, 0.2, 0) + (IF([Device Termed (TO)]@row, 0.2, 0) + (IF([Source…
-
Calculate Answers from Form input
Hi I have a forms with 16 questions, the questions have one of four answers A Rarely seen B Occasionally seen C Frequently seen D Seen majority of the time I want to calculate the total across the rows with a vaule for each of A = 1 B = 2 C = 3 D = 4 Is there a formula that can do this? =IF(([Q1]1 = "A Rarely seen"), "1",…