-
How to ignore blank cells in health formula
I have a working formula for task health based on % Complete and Target Completion Date (see below). I'd like this formula to ignore any rows with blank Target Completion Date and display nothing in the Health column if there is no Target Completion Date. Right now it's defaulting to Red. I know I should add ISBLANK, but I…
-
Pre-Filled Form URLs
Hi SS Community, I created a formula that builds the URL below to pre-fill fields on a form using values from a table. The resulting string works when I copy & paste it into a browser window, but not when I click on the link in the Smartsheet table, the last field (Personal Email) is blank. Does this mean that there is an…
-
Calculate start date based off of end date
Hi All, Can someone help me with a formula? I've searched the community and haven't been able to find an answer. I already have a formula in place to calculate the end date based off of the start date and duration of project. However, if I have an already defined end date I'd like to calculate the start date based off of…
-
Checkbox with IF AND formula- satisfy two criteria simultaneously
Hi, I want to add a formula to a checkbox column, the purpose is to automatically check the box once the cell satisfies two criteria simultaneously. Criteria 1: Phase column=Milestone Criteria 2: Finish Date column<= 03/11/2024 I have tried a few IF AND formulas but still can't get the result desired, loosing temper now :(…
-
Could someone help me with this sheet summary formula?
I'm trying to create a formula that adds the amount in the "Amount" column only if the "Revenue Period" column contains "2024". When using this formula I get "0".. =SUMIFS("Amount", "Revenue Period", "*2024*")
-
RANKEQ with a date criteria
Hi there, I am trying to pull the most recent fee payment value from each regional group. I want this to pull through to a report so that I can see that most recent payment value In a report where all the regions appear once. I have the region column (called DNO) the fee payment column (called A&D fees) and the date column…
-
Counting Dates within a specific month
I am trying to count the number of entries per month. My formula for January specifically is below. Can anyone fix this? =COUNTIF({Sourced Date}, MONTH(1))
-
Week Commencing Date
Does anyone have a formula I could use to work out the week commencing date for a date field where Monday is the start of the week?
-
How to count dates in a column? INVALID DATATYPE error
I want to count how many dates in a column called "Completed" occur in the month of January. Using this formula =COUNTIF([Completed]:[Completed], MONTH(@cell) = 1) returns INVALID DATATYPE whether I use it in a cell on the sheet or in a sheet summary field or on a separate metric sheet with the formula referencing the…
-
Formula to Count multiple text values in a range
I have 2 columns/ranges. I need to count if range1 is value text1 and range2 has values text2, and text3, and text4. This formula works so far =COUNTIFS([Range1]:[Range1], "Text1", [Range2]:[Range2], "Text2") However things get wonky when I wan to add more text values to Range 2. So it would kinda look like below. But…