-
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…
-
#INVALID DATA TYPE Error
I am receiving an #INVALID DATA TYPE Error when I have blanks in the dates I am referencing. The formula is summing the equipment pieces based on the delivery month, but the delivery date can be added later in the project, so there will often be blanks. My formula is below: Adding an IFERROR avoids the error message, but I…
-
Time formula: how to add 4 hours to my appoint time column?
Hi, I have a appointment time column and I would like to use a formula and add 4 hours to get the End time. Is there an alternative way to do it like sort of formulas? Kindly help, I need this to my report this week.
-
Using LARGE with duplicate results?
Looking to find, for example, the 10 largest projects in our system by allocated resource hours. But what happens when there are duplicates/projects with the same number of allocated resource hours? =LARGE(({total hours}), n) From the result of that formula, in another cell, I then collect that respective project's name…
-
Multiple IF Statments
Hi Smartsheet Community, I have a question regarding multiple IF statements. What I am trying to accomplish: I am trying to put text in a box if the days are greater than or equal to the number of days since completion 0-30 Days, 31-60 days, 61-90 Days, 91-120 Days, 121-364 Days, 365+ Days. Current Formula: =IF([Total AR…
-
Return a value if a cell date is between 2 dates
Hi, I want to automate one of my sheet summary by retrieving the Sprint# based on today's date. (When today's date is between the beginning and end of a sprint, I want the sprint# to be retrieved in the Automated Sprint field of the summary sheet). I have tried to use =INDEX([Sprint#]:[Sprint#], MATCH([Sprint start…
-
Using Sumifs and Month referencing another sheet
Hello! I am trying to sum the values occuring in a particular month referencing another sheet. Here's my formula: =SUMIFS({Summary Roll-Up - Machine Accepted}, {Summary Roll-Up - Machine Accepted}, >0, {Summary Roll-Up - Delivery End}, MONTH(@cell) = 1) The first part of the formula is working correctly, but when I add the…
-
Metadata sheet pulling data from 40 project plans into 1 dashboard?
I am working with sheets, already created. There's 1 project plan and several sheet i.e. a RAID log and a Decision log that go along with each project. Then there is a metadata sheet (1 per project) that pulls info from the project plan and calculates it and then it gets displayed in a dashboard. The lastest ask is a…
-
How do I account for a specific status in a formula calculating expiration status?
I am using the following formula to count the number of contracts that are expired =COUNTIF([Contract Expiration Date]:[Contract Expiration Date], <TODAY()) I want to exclude any contracts that have "Archived" (Value) as the "Current Status" (Column) from this count.