-
COUNTIFS Across Different Ranges Returns #INVALID DATA TYPE
Hi Smartsheet Community, I am stumped. I have a COUNTIFS formula that behaves as expected on my test sheet but returns an #INVALID DATA TYPE error when replicated on a actual sheet. The Column Types and formatting are the same on the test sheet and actual sheet. I would like to count the number of instances where Unit…
-
Large criterion name
Hello, I'm trying to make a formula to count the number of jobs by builder for the month of January to go into my sheet summary. I managed to get the formula working for January however when i copied and pasted the formula and changed the name of the builder i got #invalid operation. The working formula is as follows,…
-
Help with returning value from two criteria
I want to fill the empty green field by matching the yellow and blue fields. I've tried Index collect and index match match. =INDEX(COLLECT({QuantityOverview}, {PPEReceiptOverview Range 1}, Name@row, {PPEReceiptOverview Range 2}, [1 Quantity]1, 1)) returns #INCORRECT ARGUMENT SET
-
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…