-
Determining an identifier based on parent/children and an additional factor
I have a sheet where I'm tracking IT initiatives, and some are considered Cross-Platform. Some Cross-Platform records have children and others do not. (See screenshot) Based on some reports I need, I have to identify Cross-Platform initiatives, but do not want to include the child initiatives. I created a column called…
-
COUNTIFS for individual weeks
I reached out for assistance recently about formulas to count specific projects for individual weeks. The formula assistance I received counts projects from today + next 7 then 13 days then 21 days. What the formulas are not doing is only counting from day 8 to day 13 from today. Here are my formulas...…
-
Using formulas to determinate the status of a set of tasks
Hi community, I'm new on Smartsheet and I'm trying to make a formula to automatically calculate the status of a set of tasks (or subtasks). Let me explain my situation: I've created a main task, lets say "Project1" and it contains some subtasks as children. Next to this first column, I've got a column for the status (its…
-
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.
-
count values where two columns match
i am trying to count the number of matching pairs of values in two columns. eg: Column A Column B 1910 760 1910 810 1910 810 1990 760 1990 760 the outcome of the above that i am looking for is as follows 1910 x 760 = 1 1910 x 810 = 2 1990 x 760 = 2 can someone please help me with a formula that will give me these count…
-
countif
Hello, I have a formula that I'm attempting to count the number of projects within the Task Name column that have "hot CX" as part of the name. Task name is customer- Hot CX My formula returns a 0 count. What changes do I need to make with my formula? =COUNTIF([Task Name]1:[Task Name]100, "Hot CX") Thanks!
-
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…
-
collect with conditional statement to return all values
I am attempting to use a collect with no criteria, that is it grabs everything and am having some problems. =JOIN(COLLECT(Return:Return, a:a, IF(ISBLANK(a10), true))) my desired behavior is that if cell A10 is blank, all of the values of Return (including where A is blank) are collected. This is part of a much larger…
-
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…
-
Is there a way to define "today's" health, based on today's date and corresponding "% completion" va
Hey everyone, I've got a set of data pulling in completion % by date, and want to give my team a quick snapshot of overall health of the project based on completion % for TODAY(). I have used the following formula, which works for giving RYG per day: =IF(AND(Date9 = TODAY(), Difference9 < 0.05), "Green", IF(AND(Date9 =…
