-
COUNTIFS based on Children criteria of certain row
Context: Please see screenshot attached. Project Manager "Fred" has 2 "active" projects in January: Project 1 and Project 2, which are Children under "Fred". This count of 2 is determined by some percentage of the project being completed. Goal: Count the number of active projects per month based on % complete being greater…
-
Checkmark Parent if any Children Due Date is in the next 7 Days
Hello! Stuck testing out formulas to add a Column [Children Due Soon] that checkmarks if a parent row [Task Name] has any children task is due [Due] in the next 7 days. I've been trying to edit the famous RGB formula for this, but still not very familiar with formulas. Thanks!
-
Using IF, AND, OR
Looking for formula advice/help! I would like to write a formula that will: Mark Past Due if Complete By is greater than today + task is not complete If no Complete By date exists, than Past Due flag remains unchecked So far, I've been able to create a flag based on the first bullet, however-- tasks that do not have a…
-
Count "text" if the status is "Doing"
I'd like a way to count Theme column (Text Column) if "IVR" appears, AND if the Status Column (Dropdown column) is either "Backlog" or "To Do" or "Doing" I can get a simple countif formula to work, but can't figure out how to add a second criteria based on status i tried ones along the lines of =COUNTIF(AND(Theme:Theme,…
-
Validation Formula
Going crazy. My current formula =IF(AND([Time Missed / Used (In Hours)]1 < 19.99),CONTAINS("Not", [Excused/Not Excused]1:[Excused/Not Excused]1, ".25", "1"))) = #UNPARSEABLE The goal here is to return .25 or 1 into a cell "Totals" if cell "Time Missed / Used (In Hours)]" is less than 19.99 and cell "[Excused/Not Excused]".…
-
How to count data in children rows based on criteria in parent rows
Hello all. I am thinking this would best be done in a Sheet Summary but my brain seems to still be in winter break mode. Hoping someone can point me in the right direction. My Smartsheet is a list of courses (parent rows including course title and date) with participants (children rows including participant names and a…
-
Is it possible to set up an If statement on a “Child” field dependent on a date in the Parent field?
What I am looking to do is have a field fill in Yes or No based on the Impacted Article Complete Date in the Child row and the Alert Expiration Date in the Parent Row. If the Impacted Article complete Date is prior to the Alert Expiration Date, the field would fill in Yes. If the date is after the Alert Expiration Date, it…
-
Multiple IF function pull from multiple cells
I am working on a spend tracker and i am having trouble pulling currency conversions from other cells in the row. I need the 'committed amount in USD' to pull from the correct currency conversion cell depending on the currency selected. current formula i have that works with pulling from just one cell is - =IF(Currency@row…
-
Raising Flag for at Risk Tasks
I would like the flag to be red if the date is within 7 days of "today", AND, if "Status" is not "Complete", and I want the flag to be white if the "Due Date" is blank, greater than seven days away, or if the task status is "Complete" I have: IF(ISBLANK([Due Date]9), 0, IF([Due Date]9 <= TODAY(7), 1, 0)) I just don't know…
-
#INVALID OPERATION - Apostrophe catastrophe
Hi Community, I'm trying to convert some numbers expressed in KB into GB so I can sum all in GB. To do this I had to use the LEFT function to pull everything appended with "KB" from a column. That was no problem. I then took the result of that function and did a copy/paste special, so I would no longer have the formula,…