-
3 condition formula- Help
Need help with a formula, 3 conditions- 1) Recognize first 3 digits of "ticket number" (EX: "LEG", "ONB", "REV") 2) Recognize when a task is past due 3) Status not "Complete" or "Not Applicable" What I have so far: =COUNTIFS({KIM- Project Plan- TEST Range 3}, "LEG", {KIM- Project Plan- TEST Range 1}, >TODAY(), {KIM-…
-
Countif Formula to include Flags
Hello, I have a cross-sheet formula pulling in dates and other data from various sheets; however, I can't figure out how to count if the column contains a flag. The formula is summing all new hires who have a status containing "push". =IF(Level@row < 2, SUM(CHILDREN()), IF(Level@row = 2, COUNTIFS({New Hire Push List -…
-
Is a daily entry missing?
We use a form that allows each of our 120 stores' managers to input some data at the end of each day. Is there a way for me to see which stores missed a day? Each form entry requires the store name (drop down), and I have another sheet that has every store listed in it. Trying to think of a way to have a…
-
RAG status help
I am trying to right a formula to give a RAG status based on 6 columns of check boxes. I want Green for all boxes ticked, Yellow for some, and Red for none. This is the formula I have but it comes up as Unparseable =IF(COUNTIFS([PTC CRBA]@row:[PTC Aud]@row, 1) = 6, "Green", IF(COUNTIFS([PTC CRBA]@row:[PTC Aud]@row, 1) < 6,…
-
How do you reference 'Created' column date of a PARENT and add one calendar year?
Hi all, I am trying to do the following: For a given cell, I would like to reference its Parent's Created date and return a date that is one year in the future. I think I'm just struggling with finding the correct syntax.
-
Using a profile matrix to drive automation on specific dates
I have a profile matrix that has a set of dates across the top (columns), row 1 and then a list of product types (Student, Teacher, Classroom) for each record, starting at row 2. On a separate sheet I have a list of products that associate to the various product types, i.e. Student Workbooks (Student), Instructional Manual…
-
SUMIFS Formula
I am trying to create a SUMIFS formula referencing another sheet: =SUMIFS({Sheet - PTO & Project Staffing Range 2}, {Sheet - PTO & Project Staffing Range 1}, Projects@row, {Sheet - PTO & Project Staffing Range 3}, "Trainee Solicitor") The column I am trying to add is a formula so, I have tried VALUE in front of the above…
-
Can Formulas Reference Reports?
I'm trying to see if formulas can reference reports or just root SS. I ran a report for all late tasks assigned to all people in a department. I then wanted to use a COUNTIF formula that would sum up all the late tasks of a certain person. When I go to do that and the formula asks me to reference a sheet, I am only…
-
Count if NOT BLANK
Hi. I am trying to write a formula with multiple criteria to for my metrics sheets; however, I'm not sure how to add this one count if not blank. This is my current formula: =IFERROR(COUNTIFS({OHCA Form - Agency}, $Agency@row, {OHCA Form - Review Complete}, "Medical Review Complete", {2020 OHCA Form - Etiology},…
-
Automatic indentation
Hi, I am using Data Shuttle to bring data from an external source into my SmartSheet. I want to automatically indent based on the Unique ID as shown in the below example. The Unique ID with the lowest suffix should be the parent row and subsequent ones should be children. Is this possible to achieve with a formula or some…