-
At Risk If/Not formula
I am trying to build a formula where "At Risk" will be flagged IF the following conditions are met: the Status is not Gray (n/a) or Green (complete) AND the End Date has passed I tried this formula but something is missing, it comes out unparseable. =IF((NOT(Status@row = "GRAY")), 0, 0),IF(AND([End Date]@row < TODAY(),…
-
Index/Match
I have a smartsheet that has a column for "State" and then a column for "Territory". How do I write a function that if a row within column "State" equals say "TX" than the column for "Territory" within the same row = "West". I would need to do this for all states broken into 4 or 5 territories.
-
Count the total number of task a member being assigned to when one task may assigned to two people
Hi All, What formula can I use to consolidate the number of task assigned for each member? Because i unable to write a good formula, then I need to form all these possibility combination. I have more than 20 combination and it is difficult for the member to read in the chart.
-
How do I make a column formula for parent/child RYGB balls?
I need a formula to do the following for the Child row (note: I do have a Child Count Column set up): Contract Expiration Date is in the past = Red Contract Expiration Date is in the next 120 days = Yellow Contract Expiration Date is more than 120 days out = Green Contract Expiration Date is BLANK = Blue I then need the…
-
Question on SumIFS
We're hosting a "Bike to the Moon" where our supporters are uploading their daily trips, miles and emails in a worksheet. We want to display a riders' individual trips (showing date and miles traveled) and a total miles field that sums across all their trips (rows.) I'm using this formula at the moment but I'd rather not…
-
how do you reference a single cell in an external sheet...dynamically? formulas? Cell Linking?
Thank you for your time. I cant find the answer in the documentation or forum. So I am asking. a. How do I reference a single cell in an external sheet? b. Can I reference a cell by row number and column number? c. Is there a way to make this dynamic? using the ' =ExampleCell1 ' syntax is for the current sheet. How would I…
-
Formula within a formula
Good afternoon everyone, I was wondering if it is possible to do an IF formula with a date formula in the same cell? I would like to do the following, but also subtract the number of working days between two dates. IF(Status@row "Complete", 0, ..... Below is the formula I have now.
-
Simplifying SUMIFs formulas
Hi In my summary sheet, I am calculating the total of all CEs value for all my Agents using the following formulas: =SUMIFS([CEs:CEs], [Agents:Agents], "Agent1") + SUMIFS([CEs:CEs], [Agents:Agents], "Agent2") + SUMIFS([CEs:CEs], [Agents:Agents], "Agent3") + SUMIFS([CEs:CEs], [Agents:Agents], "Agent4") and it gives me the…
-
Help with Count If formula on unique values only
I am trying to add a count to my sheet summary which gives me the number of contracts which are a certain status within my sheet eg. pending, signed & returned etc but each contract can be listed a number of times within the sheet and I only want the contract to count once in my summary. For Example: These entries are all…
-
Trying to Input a formula to automatically generate a symbol
I have a column where I am tracking percent met and I want that percent to trigger a symbol in the column next to it. The symbols I am using are the 5 circles (Empty, Quarter, Half, Three Quarter, Full). This is the code I tried =IF([Percent Met]3, =100, "Full", IF([Percent Met]3, 99.9 > 75 "Three Quarter", IF([Percent…