-
ISBLANK combined with AND statements
Hi, I'm having trouble nesting AND & OR statements with the ISBLANK condition in my IF statement. I have two columns: [Agreed End Date] and [Actual End Date] =IF(AND(NOT(ISBLANK([Actual End Date])), (OR(TODAY() - [Agreed End Date]@row < 1, "On Track", IF(TODAY() - [Agreed End Date]@row < 7, "Delay", IF(TODAY() - [Agreed…
-
Across 3 Sheets -> Count When Index Match (Cross Sheet from Sheet 2 to Sheet3) Equals Cell Value
I have 3 Sheets: Sheet1: Metrics Sheet2: List of Addresses (without States) Sheet3: List of Zip Codes and their States Without adding a column to Sheet2, what is the correct formula (to be placed on Sheet1) to count the number of rows on Sheet2 that have the same State shown on Sheet1? Sheet1 Data State | Count LA | count…
-
=IFERROR Issue
Hello, I have an arithmetic formula that sometimes gives me a number, and other times does not depending on if the [Completed Milestone by Month] columns are populated. When they are not populated they result in an error "Divide by Zero", which is fine. However I'd rather this not display on my dashboard. I've tried…
-
Due date formula based off priority
I'm trying to have calculate a due date based off the priority (high , medium, or low), add 1,2, or 4 days to the date submitted. I'm using something like the below, but getting an error. Any help? =IF([Business Priority]@row = "High"), [Date Submitted]@row + 1, IF([Business Priority]@row = "Medium"), [Date Submitted]@row…
-
SUMIFS within Date Range
Hi, I need help creating a formula to summarize an average using Sheet Summary Columns, I have the following sheet columns: Current Date, Average Rating, I have the following summary columns: From Date, To Date I have the following sheet summary column (locked) for our formula: Total Average Rating I'm trying that the From…
-
Count the number of times values appear in a multiselect column - depending on a value in other cell
I want to count the number of times values appear in a multiselect column with the condition of a value appearing in a different cell . for example: we have a fruit multiselect column and day of the week when we bought the fruits. I want to count how many times apple appears in a fruit multi select column that we bought on…
-
COUNTIFS Blank & Not Blank
Hello, Formula shouldn't be too difficult, but I'm having some issues. What is the syntax for a function that counts is a cell is blank within certain criteria. Here was my attempt, but it was not working. Will also need the notblank version. =COUNTIFS({Domain}, [Primary Column]@row, {Metric}, ISBLANK({Metric})) Thank you!
-
Two new PPM Functions: Successors and isCritical, available now!
Hi Community, We’re excited to announce the launch of two new PPM formula functions available now in Smartsheet: Successors and isCritical. The Successors function (type: numeric) allows users to calculate the direct successors of a task and return a collection of values that will occur as a result of the referenced task.…
-
Formula for task health
I am looking for a formula to identify task heath, based on duration, % complete and target due date, where the health is based on duration of task, such as 5d and if there is enough time to complete the task and the %completed - so a 10d task that is more than 10d before target date would be green. That same task would…
-
Formula to compute costs based on months to only include months between the EndDate and GoLiveDate
I have a sheet to compute cost based on EndDate and GoLiveDate. If the StartDate is less than GoLiveDate then cost is sum of monthly cost between StartDate and GoLiveDate. Project Name: Test 3 | StartDate: 5/29/2022 | GoLiveDate: 9/7/2022, Cost: May cost + June cost + July cost + August cost + September cost. Test 4 |…
