-
Formula to retrieve most frequent number
Hi all, I believe I'd use MODE for this in Excel but can't find an equivalent in Smartsheet. I need to build a formula to retrieve the most commonly found numbers from a column in a referenced sheet. Does anyone have a suggestion for how this can be done without MODE? For example, if I have a column in another sheet that…
-
Capture modified date and time stamp
Hello. I have an system column that records modified date and time I also have one that records when the entry was added to the Smartsheet. We have 4 hours to respond to a new entry. What I would to do is when the status of a row changes to reviewing it would record the date and time stamp then we would use a formula to…
-
Formula help for averaging data in 1 column based on criteria from 2 other columns
I need help with a formula that will average the total in 1 column based on 3 criteria of another column and 1 criteria of another column. To phrase better, I have a column of dollar amounts that I need to average based upon the sum of all values in that column. BUT, the values to sum are based upon 3 status values of…
-
Extract date as date from date and time stamp
I am importing a date and time stamp (e.g., 9/3/2022 8:36:19 AM) and need to extract the date into a date field so Smartsheet recognizes the date. I have a formula to extract the date as text (i.e., 9/3/2022) in another column, but cannot find a formula that works to convert the month and day to two digits and converts the…
-
Nest WORKDAYS from TODAY into IF formula to return correct symbol
Hello! I'm VERY new to this, but I worked out my first nested IF formula to pull symbols based on Start & End Dates. =IF([Done?]@row = 1, "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(3), "Yellow", IF([Start Date]@row <= TODAY(), "Green", "")))) Logic... "Gray" IF task marked Complete "Red" IF End…
-
Mark Duplicates In Multiselect Dropdown Contacts Column vs Employee Names List?
Ok, we're making a labor schedule, and want to be able to note when and where someone is used multiple times so we can prevent overscheduling someone and ensuring proper manpower. With the above as an example, I want to highlight a cell in [Employees on Job] (multiselect dropdown contacts) column, when one of the values in…
-
IS BLANK on INDEX and MATCH
Hi, I am struggling with a formula. I am trying to lookup a value from another sheet, based on a Supplier name in a column on the sheet where I require the value returned. If the Supplier is blank on the sheet that I am referencing, then it returns the first blank cell on that column instead of just an error or blank…
-
I'm stuck on a COUNTIFS formula
I'm stuck on a formula trying to get a total that only looks for specific criteria. This is what I wrote: =COUNTIFS(Status53:Status114, NOT(HAS("Backlog", Priority53:Priority114, "High"))) It's not getting the the info I need. I'm trying to find out what tasks have a "High" Priority but aren't Status "Backlog". I'm sure…
-
Can someone help me figure out why my multiple IF formula will not work?
=IF([PO Type]@row = "Standard With Quote", [Quote Amount (ESTIMATING)]@row - [PO Amount Including Change Orders ]@row),IF([PO Type]@row = "Non Standard No Quote", [Original PO Amount (Labor Only If Req) (PO Admin)]@row)) Everything is fine until I add the second "IF" statement. Thanks in advance.
-
Count the number of times a word appears in a column
Hello everybody! I need help with a formula that I am not sure how to enter for what I am looking for. I am building a dashboard on certain metrics that align with a key result. For this we have several results, two of them being Establish Success and Success. I need to know the number of projects that align with Establish…