-
IF Statements Date Range & Excluding Blank Cells
I need to build a status column that registers when any due dates in a project are nearing certain parameters. This is for projects that could have between 1 and 10 due dates. Formula at the bottom (it's a doozy). * Green if any due dates in a row are > than today(+7). * Yellow if any due dates are between Today(+7) and…
-
SUMIF(OR(???
I am trying to sum a column if either my Paid in 1st half in 2019 column is checked or my Paid in 2nd half in 2019 column is checked. I am trying to use a few different formulas without success. Here are a two that I've tried: =SUMIF(OR([Paid 1st half in 2019]:[Paid 1st half in 2019], [Paid 2nd half in 2019]:[Paid 2nd half…
-
RYG based on Done Check box and Due Date
I want to write a formula to turn ball red if done check box is unchecked and the date is less then or equal to today minus 2, yellow if unchecked and the date is greater than today -3 and less then today -5, and green if done box is checked =IF(AND(Done7 = 0, [Due Date]7 <= TODAY(-2), "Red", IF([Due Date]7>TODAY(-2),…
-
#INVALID DATA TYPE adding MONTH to COUNTIFS with cross-sheet refs
Hello all, using the following formula: =COUNTIFS({TACTICS design resource}, AND(@cell <> "", @cell <> "N/A"), {TACTICS status}, AND(@cell <> "Cancelled", @cell <> "On hold"), {TACTICS status}, <>"admin", {TACTICS project number}, <>"*N/A", @{TACTIC actual delivery date}, MONTH(@cell) = 12) I'm getting an #INVALID DATA…
-
Help with formula to populate a quarter (text) based off date field.
Hello! I've searched many posts on the forum and can't find a solution. I'm at my wits end, any help would be very much appreciated! I am looking for a formula that will show a text result of Q#-YYYY based on an entry in a date field ("Expected Delivery Date", which is using the standard SmartSheet Date column. I can't…
-
If multiple cells are not blank - check the box
I figured the formula to check a box in a column if one other column is not blank, but I can't figure out how to check the box if multiple cells within a range are not blank. I'm using this formula: =ISBLANK([Monday | Start Time]1) = false I have a form that feeds into a sheet that has options for meeting start time,…
-
How do I return a date based on a dropdown value?
Hi all, I am trying to work out a formula to get a cell to return a date based on a dropdown value. I need the cell to tell me when a matter is due, based on when the matter was received and the priority rating. the priority levels are (Service Type): * Fast track - 7 days * Basic - 14 days * Standard - 30 days * Complex -…
-
Count of Completed Tasks by Month across multiple sheets
I am trying to get a count of completed tasks by a team by month across multiple sheets. I've created this formula in the sheet summary: =COUNTIFS(Status:Status, "Complete", [Team Assignment]:[Team Assignment], "Lean"). If I'm trying to get a completed count of tasks for the month of September, how could I get that count?…
-
Countif range from multi select contains (value) under each parent from another sheet
I need a cross reference formula using countif a specific value in a multi select column is under a parent from another sheet.
-
Autofill formulas not applying per conditions
I have two formulas in my sheet, one that populates with different symbols and one that triggers a checkbox. One formula is using reference to @row while the other is using a specific row number. The formulas are applied to all rows in their respective columns. Neither of the formulas is getting updated when a new row is…
