-
Conditional formatting and uniquely identifying cells
Hi, I would like to only apply conditional formatting to cells that are percentages. My rules currently set any cells that are over 100% as red, and 100% or less as green. Due to the red rule formatting any cell over 1.01 a lot of my data is showing as red, wheras this really needs to be white, there is no cause to flag…
-
SUMIFS function
I would like to the sum of one column based on a specific date range in another column. e.g Need to total costs for June 2018. I am using the following formula =SUMIFS(Cost3:Cost29,[End Date]3:[End Date]29,”>=01/06/2018″,[End Date]3:[End Date]29,”<=30/06/2017″) However it is coming back with an error #UNPARSEABLE.
-
COUNTIFS between dates & criteria.
Greetings Smartsheet Community, I am attempting to cross reference another sheet and Count all the Completed tasks within a certain time frame. So far I am successful counting the dates in the range using the following formula: =COUNTIFS({Range 1}, <=DATE(2018, 4, 31), {Range 1}, >=DATE(2018, 4, 1)) Next I'd like to add…
-
cell reference based on date
We have a yearly schedule for all our field guys that is updated a week prior and sent out to the field so everyone knows where they will be working for the upcoming week. My question is can I tie a roll up sheet to the master schedule sheet that I would input a date on the roll up and based on that date grab the matching…
-
Formula that will add 1 to a Qty row
Newbie to Smartsheet...I am wanting to write a formula that would at a quantity of 1 if the prior column has a date in the cell. I am trying to calculate the # of installations. basically if a date is entered into an install date column, I need the formula to add a quantity to a install quantity column. thanks, sGF
-
How to select value from last column in a range
Hi! Would love to get feedback on how to create a "Current Status" formula that looks by row across columns to pull the latest value. What I want is a formula that updates automatically as I populate columns with weekly Account Status. Below is a screenshot that shows the columns I am working with. The leftmost column is…
-
Excel @Text() Functionality in Smartsheet?
Is there a way in Smartsheet to use a formula that gives the same result as using @text() in Excel? I have a spreadsheet that I need to concatenate several fields together and have the field then display as text. Most of what I see now for formulas is to covert from text into formula. But I need to go the other way? Use…
-
Conditional formatting with heirarchy and card view
Hey All, I'm having difficulty getting my card view tabs to show a certain color based on percentage complete for the subtask. When I create the rule it either highlights all the subtasks based on the parent percentage complete, or does nothing at all. I am trying to have individual subtasks change color base on…
-
% Complete based on Todays date, Start and End dates
I'm looking to return a % complete based on the start and end dates and the current date. So if a task starts on the 17/7 and ends on the 31/7, and today is the 24/7 - the % complete would be 53%. - (I want to have this as a separate column so I can keep dependencies on the actual % complete column) =(MIN(TODAY(),*finish…
-
COUNTIF/SUMIF items with number of workdays between a date and a date range is 3
Hi! I would like to count a number of entries in a sheet if their due date is a certain number of work days before a date in another sheet. I tried to use the following formula: =COUNTIF('reference to a list of entries column in another sheet', NETDAYS('reference to due date column in another sheet', 'cell with a date in a…
