-
Find Latest Date for With a criteria in rows
I have a form that my field team inputs the action they make in customer, the same form is used for every customer, In Another sheet, I have the visit controle, and in this other sheet, I want to know the last visit my team made to each customer. So I need to use a MAX formula for each specific customer. The only solution…
-
How do I use CountIF to count items 30 days or less from Today without counting items in the past?
I have a CountIF formula set to count the number of cells that have dates 30 days or less from Today's date using the Today function, however it is also counting dates that are in the past. I only want to count the number of dates that are 30 days or less in the future. Any way I can exclude past dates? Here is what I am…
-
Formulas for performance color formatting with benchmarks
I want one of the five benchmarks to be highlighted in correlation to what is “performance”. For this example, how can I get Benchmarks – 2 Star background color changed? The benchmark ranges are 0-54%, 55-63%, 64-70%, 71-77%, and 78-100%. I have 9 metrics to do this every month. Open to other ideas as well. I just need to…
-
Nested If SmartSheet Formula For Symbols (R, G, Y)
=IF([% Complete]@row < 0.7, "Red", (IF([% Complete]@row = 1, "Green", "Yellow"))) i.e. =IF(data < number, "TRUE Symbol Text", (IF(data = number, "TRUE Symbol Text", "FALSE Symbol Text")))
-
How to attribute work completed to an employee
I am trying to find the correct data type and formula to connect complete work (that is reported via form) to the employee who completed the work - in the sheet summary. I'm looking for daily completion per employee, as well as options for weekly, monthly, and all historical in a data roll up. Once I get the formula the…
-
How to create a monthly reporting chart from summary report?
Hi, I have created a summary report from my sheet where I've month year, total cost and total test amount. When I'm trying to create a chart without grouping it any additional entry shows up as a new entry in my chart. If I group it then the months does not remain chronological. I'm attaching a snapshot of what the issue…
-
Using SumIFS and OR
Hi All, I am having some syntax issue that I can't get around. I have the following formula referencing a separate sheet. =SUMIFS({Closed Transaction Qty}, {Closed Line Category}, [Item Category]@row, {Closed Line Del.Yr}, Year@row, {Delivered Month}, [Month #]@row, OR({Load Dock Ind.}, ="A", {Load Dock Ind.}, ="NOS")) It…
-
Assistance Needed (#INVALID DATE TYPE)
I have a sheet with multiple rows and columns. Two of the columns are for Recruitment Stage and Recruitment Priority. Recruitment Stage has (7) dropdown values, and Recruitment Priority has (6) dropdown values. I'm attempting to use a COUNTIFS statement to produce a total count of partners who are either in the…
-
Count(Children()) Not Counting Correctly
I am trying to create a rollup of the status of the phase using a modified version of the method from this post: The problem I am running into is somehow the simple COUNT(CHILDREN()) portion of my formula is not counting the correct number of children. As shown in this screenshot, there are clearly 3 children of this…
-
Sum Formula when a Formula is used to Generate a number
I am trying to sum Cells that contain a number that is generated from another formula. However, the sum is not generating. These are the steps I have followed. Does anyone know how I can fix this? I have a series of cells that are populated through a form as Yes or No questions. If a question is answered yes it is assigned…