-
RGYB status - Return value based on blank cells in row
I have a table where we have to report the status of a task for each month as the year progresses, so there is a column for each month and an overall status column (see image). We're using RGYB symbols to reflect these statuses. I'm having trouble figuring out the formula to pull the status of the latest month reported.…
-
Attempting to Forecast Ongoing Projects
I have a Smartsheet with start and end dates for given projects. I am trying to create a calculation Sheet that will give me the number of ongoing projects within a specific month and year so I can forecast the number of ongoing projects we will have. I tried several variations with the "COUNTIFS" function, but cannot seem…
-
Cell selection one cell over another with increasing decreasing dates and blank cells
Trying to update the below formula to work with an additional column This was the formula i was using = IF ([approval actual]@row <>"", [approval actual]@row, IF([approval predicted]@row <>"", [approval predicted]@row, [approval revised]@row)) With the addition of the approval column I'm not quite sure, it tried some…
-
Help with Date Auto-Populate, Countifs & Reporting
Hi Team, I hope everyone is well! My name is Patrick and I'm a new SS user and was wondering if I can ask for help with the following: 1 Auto-populate the date if columns Agent and Ticket# are filled in. 2 Countifs formula is not capturing 07/11 3 Chart Reporting date to auto-change weekly based on next week's dates Please…
-
Simple question, How do I automate my RAG status column based on dates?
This is for contract management circumstances. Green=Contract is active no issues Yellow= Contract is due to expire in 90 days (I have a column with the contract expiration date in it) Red= Contract is expired (Again this would be based on the value in the contract expiration) I checked the Automation and there doesn't…
-
COUNTIFS from one date through today with other criteria
Hello! Needing help creating a formula. The formula has to count all the cells in the "clearance date" column that are blank with the criteria from the "application effective date" which is all dates happening 10.1.2021 through today (which means counting on a rolling basis) I have this formula below that isn't working:…
-
Formula for 2 criteria
Hi, I'm trying to come up with a formula to count all the students in grades K, 1, 2, 3, 4, 5 who are also in our life skills program. I have the formulas separate =COUNTIFS(Grade:Grade, ="K") + COUNTIF(Grade:Grade, <6) =COUNTIF([Life Skills]:[Life Skills], 1) I tried =COUNTIFS(Grade:Grade, ="K") + COUNTIF(Grade:Grade, <6)…
-
If formula evaluating wrong statement
Hi Everyone, I was hoping I could avoid Support for this one - not sure what I'm getting wrong here. I have an if statement, if a certain cell states "no" then I want it to return blank, otherwise I want it to evaluate an index-match formula. What I'm running into is that even when the cell is equal to no, it's still…
-
What's wrong with this formula?
=IF(Due date,<=Date closed, "yes", "[No]") the intent is to record yes or no in a helper column dependent. The former if the date closed is on or before the due date. The latter if after the due date. Thanks in advance
-
COUNTIF(S)/AND - Three criteria, two columns
Trying to count based on criteria in two columns, Shipped Date column (Month/Year) and Activity column (System). Using Month/Year criteria works using "COUNTIF", but when I change to "COUNTIFS" to include additional "Activity" criteria, not having any luck. Any suggestions are appreciated! =COUNTIF([Shipped Date]:[Shipped…
