-
Insert "Or" Statement in a INDEX(COLLECT), CONTAINS
Hi Team, I need to insert an OR statement into the following formula. I would like to modify the formula to find one of two values, whichever it finds, populate. The current statement: =INDEX(COLLECT({Interface Raw Data Range 1}, {Interface Raw Data Range 1}, CONTAINS([Interface Name]@row, @cell)), 1) - ***This formula…
-
COUNTIF and CONTAINS for a *multiselect* Contacts column?
Hi -- does anyone know if it's possible to use the combo COUNTIF & CONTAINS on a multiselect contacts column? Use case: we have projects that involve multiple resources, which we've captured as a multiselect contacts column. I'd like to report on how many projects each resource has, which means I need a count of all of the…
-
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…