-
Red / Yellow / Amber formula
Hi, Can anone please suggest for my sheet that is formatted as shown in the attached image and for the following conditions if risk/issues cell is not blank and due date is in the future then yellow if past due date then red if past due date and with risk/issue cell not blank still red if not past due date and no…
-
How do I count occurrences of a word in a row?
I'd love to count the number of times the word "below" occurs in a row. Apologies for the simplicity of this question, I'm very new to Smart Sheets! This word could occur as either "below target" or "significantly below target", but I'm hoping just to count ALL instances of "below" once if that's possible. I've tried…
-
Partial Text Search in Range - Index/Match
I have searched for solutions and getting weary. Challenge find PO# (a 6 digit alpha-numeric) in a string of text (GL_Remark on another sheet to index the actual value cleared general ledger. I had this formula working in Excel: =INDEX(General Ledger_AmtPaid,MATCH("*"&[@[PO/INV]]&"*",GL_Remark,0)) In SmartSheet Only finds…
-
Help pulling data from certain date range
I am currently pulling data from a mainsheet into a dashboard via a metrics sheet. Its working fine but I have been asked to start only showing data from the last 30 days on the dashboard. I am having problems figuring out how to add a date formula to my current formulas. An example of my current formula is…
-
=ROUND(SUMIF
Hi =ROUND(SUMIF($[Pareto Category]$32:$[Pareto Category]558, Predecessors21, $[Actual (MHrs)]$32:$[Actual (MHrs)]558), 0) The above is my formula. It says, "IF the value in the Pareto Category column is the same as the value in the cell Predecessors21, then count all of the values in the Actual (MHrs) column". However, I…
-
Sumifs with a condition on multi select column
Can anyone help me to write a formula of Sumifs. I am facing issue since in week column i have multiple select property used. I want to sum the time required if member X worked on week 1. Similarly time required for member x wherever week 5 is mentioned.
-
Develop formula to break up project ranking figures into 4 quadrants
Hello! I have developed a format for my team to utilize a ranking process for incoming continuous improvement ideas. The methodology is called RICE Ranking which you may or may not have heard of. Within the ranking, we want the info to automatically designate which quadrant the idea falls into based on it's rank which…
-
COUNTIF Function Within Date Range
Hello, I am trying to write a COUNTIFS formula that would pull within a certain date range. Right now, the criteria would be to pull from a specific Project Reach and then an expiration between 1/1/2021 and 1/31/2021. The formula I have right now is not producing a count, so I'm doing something wrong: =COUNTIFS({S2G Master…
-
Can't seem to get vlookup to work
I have a master sheet and a secondary sheet. In the master sheet I put in all my data such as the “Name of the Project” and “Asset Type”. This master sheet is already pretty complex and has too many columns already. In the secondary sheet I want to keep track of a different set of data related to these projects but some of…
-
COUNTIFS with OR included
Good Afternoon, I've got a doozy of a formula that I just can't figure out. I've been on the phone with smartsheet and it is still not counting correctly. Here is what I need it to count: -Range 1=on or before 12/1/20 -Range 2: Consultant name listed in the row -Range 3: Blank OR (on or after 1/1/21) Here is the formula I…