-
Multiple IF ANDS criteria
Hello! I'm trying to write a function for 3 criteria with if ands, "Approved", "Not Approved" and "Pending". Not sure im using if and correctly. It's a document approval process which requires 5 individuals to review a document. I want the status of each document request to be shown per individual and then have the overall…
-
Comparing dates IF/AND
Hi, I am trying to create a formula for: If Date1 is less than 2/29/2022, then cost for the month is Feb2022 at row value. If Date1 is greater than 2/29/2022 and less than 3/31/2022, then cost for the month is Mar2022 at row value. Formula below isn't working: any help on what I am doing incorrectly is much appreciated.…
-
Help with a formula that I need to return the result of Green for 2 reasons
Good day: I have my formula mostly figured out (see below). I want the result of green if my % Completed is 100 or if the End date is >15 days from today. How can I accomplish this? =IF([% Completed]@row = 1, "Green", IF(ISBLANK([End Date]@row), "Blue", IF([End Date]@row < TODAY(), "Red", IF(([End Date]@row - TODAY()) <=…
-
Countifs formula for checkbox column has suddenly stopped working
Hello All, My Countifs formula that counted when a box was checked in one of the columns has stopped working, giving me an invalid value error. I'm not sure if it was a recent update or what, but it has suddenly stopped working. The Countif formula checks if the value in the checkbox column is checked: =COUNTIFS([H1]:[H1],…
-
Split text to multiple columns
Hello! My team is using the JIRA connector to pull ticket info from JIRA to Smartsheet. In the JIRA description field, the team has a template that contains several key pieces of information that I would like to separate into multiple columns. Basically, I would like the ability to pull everything to the right of the : for…
-
Multi-select dropdown column to pull single value in another column
Hi Everyone! I need some assistance with a formula to extract a single value from a multi-select drop-down. It's a bit annoying as I'm sure that I can use an IF statement, but I was struggling to develop a column formula to use either HAS or CONTAIN. I'm sure it's just early morning brain, preventing me from getting it…
-
Nested IF Health Status Needs
I'm working on a formula to give me a health symbol based on today's relation to the due date and factoring in whether the task has already been completed (based on the presence or lack of completed date). I've messed with it a few times, but it's giving me an #INCORRECT ARGUMENT SET error. Here is my formula: =IF(AND([Due…
-
Past Due Calculation
Hello - I'm trying to build a formula to calculate if a row item is past due based on a value in a due date field. The columns with data are: Approved/Declined Date and Past Due Date. I'm looking for the formula to calculate if Approved/Declined Date is blank and after the Past Due Date to come back with a value of True.…
-
Correcting WeekOf and Avg Formulas
Hi all! I am trying to do 2 operations on some columns I have in a sheet. I have written (with your help ofc!) 2 formulas to fit my needs, these are a week of, and a avg formula. To be more specific, I have a sheet that has tasks on it. Assigned to these tasks are days it took to complete them, as well as the day of…
-
Counting values in a range
I have a column of numbers and I would like to count when the numbers fall between a range of numbers. For example, I would like to count numbers between 5 and 10. So, what would the formula be? This is what I have tried: =COUNTIFS({P&P report - AH - 15 Jun 22 Range 2}, "<5" ">=10")) Please help :)
