-
How to edit filter to include YEAR function
So I had this formula created and its searching by a couple of different metrics to gather the average of a column but only via the supervisor name for another column. Silly me forget we only need to spit back data from 2024 as the sheet collects multiple years worth of data and my brain is really struggling to add this…
-
Formula to automate the symbols on a sheet.
The Task Progress column is linked to the % Complete column. when someone inputs a value of 100% in the % complete column, the task progress changes to a Green dot, and the same goes for when the % complete is 0% - Red dot, 1-99% - Yellow dot. I would like to add a gray dot in the mixture so I need a new formula to reflect…
-
Why does Smartsheet add a single quote before an integer that begins with 0?
A single quote keeps getting added when a 0 precedes an integer regardless of the entry method for the cell’s value (e.g., when the values are imported into Smartsheet from an Excel file, when the values are copied and pasted into Smartsheet or when the values are manually entered). Example: 099999999 changes to ‘99999999…
-
Getting Same Resutl with Slightly Different COUNTIF
Hi Guru's, I run the below formula and it gives me the correct number however; I modify the formula to look at another range however; I get the same result as the first formula minus the 2nd range. 1st formula =COUNTIFS([Operational Priority]:[Operational Priority], ="🔶", Status:Status, "In Progress") +…
-
Need help with Nested If Statement
I need some help with a nested formula. I've spent hours on this and cannot seem to get it to work. I'm basically looking for the following function: If date column 1 and date column 2 are not blank, then, If these dates (date column 1 and date column 2) are the same, enter 0, if not, return the number of days between the…
-
Countifs not working for project by month
Hello all, I want to count 3 different project types, by the number our team does each month. Two of the 3 project types are coming back with data. Why am I getting an error on the other? Here is a snippet: I keep getting the invalid data type error on it.
-
Searching keywords from a list of words?
Hi, I have two sheets, say Sheet 1 and Sheet 2. Sheet 2 contains a list of words in one column (i.e. "Keyword"). Sheet 1 contains a column with status updates (i.e. "Comments"). I would like to set up a formula that searches if Sheet 1's "Comments" column has any matching keywords from the list of words hosted in Sheet 2's…
-
Get dates and conditional sum of budget spreads
Hi Community. I have a spread of budgets but I want to retrieve the start and finish dates of the spread AND I want to get the planned sum of the spread up to a given date. Here is a print screen of the simplified table: The Start Date and Finish Date columns is supposed to have the formulas that read the start date and…
-
I need an averageifs formula but it seems as though that is not an option is there a work around?
I need the average of my survey results if the created date falls within a certain time period as well as if well as team participation. The following is subset of my data.
-
Having trouble comparing two cells
I'm having some issue with comparing two cells in my Smartsheet. I want the box to be checked if the cells match, and if not, the value of the one on the left should turn red. I have 8 sheets for 8 different budget accounts that are all based on the same template. This works for every one of them except for this one. In…
