-
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…
-
#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…