-
Return the NETWORKDAYS of 1 month of a 2-month duration
The goal is to count the number of days that an Employee is not assigned work within a given month (MONTH A, MONTH B, etc). For example, a given month has 23 working days; I could use NETWORKDAYS of the duration to count the assigned days of that Task; say 19. To reach my goal, I would simply subract the NETWORKDAYS from…
-
Formula Needed for Parent to Inherit Child Value
I don't think this is possible, but I need a formula where a parent row Status value reads "No Active Projects" until it has a child row below it with an "In Progress" status at which point the cell should just be blank. See image below. I use the "No Active Projects" marker to determine whether or not that department…
-
Unique list of values w/ VLOOKUP or INDEX/MATCH not working
I am trying to use the aforementioned functions to crate a list of unique values by referencing a separate sheet. The source sheet contains a column of date values and a column of checkboxes which indicate unique dates. using either functions indicates #NO MATCHES which is incorrect. Please let me know how to make this…
-
Sum based on drop downs across multiple sheets
I have multiple sheets that all lead to a "Totals" sheet on the first sheet. This project is based on caseloads in a school system. Each "sheet" is its own school in the system. I have a drop-down on each sheet for the speech teacher for that school, as well as total hours of service time needed at each school/week. I need…
-
Formula off multiple columns
I'm trying to build a formula that works off multiple columns to give one of the 6 state pain scale results (No pain -- extreme pain). Here's my formula:IF(Cancel@row = 1, "No Pain", IF(Suggested@row = 1, "No Pain", IF(TODAY() - [Date Requested by School]@row > 14, "Extreme", IF(ISBLANK([Airline Contract Attached to…
-
Countif but only if?
So I am trying to count the number of times a person worked on a specific task. I have various "types", each person might have worked on the task in a different role (Lead SME, SME 2, SME 3), and then I have a check mark whether or not it should be included in the "tally". Ultimately, what I am trying to do would be if the…
-
Counting Quantities of Numbers
I am trying to build a column to count the # of times a predecessor appears in the predecessor column. The below formula has almost gotten me there, but it is also counting predecessor cells that include 1, but not exactly 1 (like 12, 21, 100, etc...) =COUNTIF(Predecessors:Predecessors, FIND(1, @cell) > 0) Is there any way…
-
RAG Status - Formula
Hi, I have currently formulated the following formula, =IF([% Committed to Date]1 - [Project Status %]1 < 0.25, "Green", IF(AND([% Committed to Date]1 - [Project Status %]1 >= 0.25, [% Committed to Date]1 - [Project Status %]1 <= 0.75), "Amber", "Red")) which is working efficiently, but problem occurs it shows me the…
-
Formula Help!
Hi, I'm new to Smartsheet and the community. I am trying to trigger a flag on my sheet to alert me when: - The status of my project is not complete, 5 days before the end date - The status of my project is in progress, but less than 50% complete, 5 days before the end date The below both work individually but I'm having…
-
Counting total based on Status & Date
Hi, I'm trying to calculate the total value of all the won job in this financial year. I have to reference 3 columns for this (Date submitted, Value, Status) not sure how to make the formula. This is as far as I got. Please help. =SUMIFS([Tender Due]:[Tender Due], >DATE(2018, 6, 30), <DATE(2018, 6, 30), Status:Status,…
