-
Stumped on a formula: multiple criteria COUNTIF
I need a COUNTIF formula pulls results for a particular status result (i.e. Complete, In Progress, etc.) for all items one level down the parent/child heirarchy from a referenced parent cell. My team has multiple projects running in parallel that need to report out their progress. I was able to give a simple update for all…
-
cross sheet references from 1 sheet to 20 sheets
the current Cross sheet formula below is 1 sheet, due to an error message of " to many references" i split 3 years into quartley sheets ex" 2020 Q1/2020 Q2--2021 Q1/2022---2022 Q1/2022 Q2 ..ETC I need a formula that will cross reference all the Quarterly sheets ex: {Q1 2022 agrmt Type}/{Q2 2022 agrmt Type}/{Q1 2021 agrmt…
-
OVERDUE Formula
Hi I would like to say: if the completed date is greater than the end date put "overdue" in the column If completed date is empty and it is after the end date put "overdue" If the end date is empty leave empty This is not working =IF(OR(AND(ISBLANK([Completed Date]@row), TODAY() > [END]@row), [Completed Date]@row >…
-
Validate Multiselect Dropdown List
Hi, I have a dropdown list where people enter several answers manually and there're around 2,000 different legitimate answers . I have a different sheet that has all the correct answers. Is there a way to validate that all answers are correct, and to get an error message if one is incorrect? Thanks,
-
Stuck on completed in the last 7 days formula / function
Hi I keep getting incorrect augmentation for this cross sheet formula =COUNTIF({Sheet 1 Execution completed}, =TODAY(-7), +COUNTIF({sheet 2 execution completed date}, =TODAY(-7))). When I out <= Today(-7) I got numbers that did not exist. I am new to smartsheet and trying to learn via youtube and here. Many thanks in…
-
Convert Numbers (Days) to To Year/Month
I'm trying to convert number of days to year and month. In excel I would use the following formula =DATEDIF(0,[Days closed]11,"y") &" years, "&DATEDIF(0,[Days closed]11,"ym") &" months" Though this formula does not work in smartsheet is there another way to do this
-
COUNTIF/AND/THEN CHECK A BOX OR SYMBOL
FORMULA 1-I need a formula that will "count 5" check boxes and a "contain" to either "check a box or a symbol."
-
Average if when date is current year
we want to update the formula to show the average # of problem solving days when the idea is a JDI and the opportunity was submitted in the current year. Current formula - missing component to reflect only submissions from current year. =AVERAGEIF({APD CI Tracker Range 5}, ="JDI", {APD CI Tracker Range 4}) {APD CI Tracker…
-
Need to add on if "Yes" leave blank to my formula but cannot get it to work
Hi, I have been assigned smartsheets and am trying my best to train myself via youtube + the forum. I created this formula =IF(ISBLANK([Completed Date]@row), "", IF([Completed Date]@row > [END]@row, "No", "Yes") It shows if the completed date is blank: leave blank if the completed date is greater than the end date put no…
-
Formula to see if completed date is within the last seven days of the final acceptable date
Hi, I have a due date (which is a guidance), and then a final acceptable date. I would like to see what items are completed on the last 7 days (including the last day) of the final acceptable date. If it is completed in the last 7 days of the final acceptable date it will be "yes" , otherwise blank. Any help would be…
