-
Countifs that aren't certain status and are within specific time frame
I want the formula to show how many items are assigned in the next two weeks that also are not marked Complete, Cancelled, or Hold. When I use the first part of the formula on its own it works, but I want it to show only those within the next two weeks. =COUNTIFS(Status:Status, NOT(OR(@cell = "Complete", @cell =…
-
Join Collect Function not working
Hello! I have this formula and it doesn't seem to work: =JOIN(COLLECT([Capacity-Space Planning]2:[Capacity-Space Planning]245, [Property Name]2:[Property Name]245, [Property Name]@row, Space2:Space245, Space@row)) I'm not sure what I'm doing wring, it's not bring back anything. Help!
-
Copy Rows Workflow
Hi, I want a sheet to copy all new rows from one sheet to another master sheet but my original sheet has an Auto-Number/System column in to give every row a reference number, when the workflow runs it creates a whole new column at the end of the data because it won't allow me to have the column it should go to in the…
-
Risk Management Formula
I have two columns named Probability and Occurrence. Both the columns has a closed list. User have to select from Very Low (1), Low (2), Medium (3), High (4) and Very High (5). When the users select medium from one column and high from the second column then the third column should multiply the 3 x 4 = 12. The third column…
-
SUMIFS and Referencing Another Sheet (Financials) = INCORRECT ARGUMENT SET
Hello all, I'm trying to compile some financial totals for various departments into a Metrics sheet for Dashboard reporting and something is not going right. I can get the total for all departments from the referenced sheet with =SUMIF({Total}, but when I add the criteria for each department I get # INCORRECT ARGUMENT SET.…
-
My simple NETWORKDAYS is not correct. I'm stumped. :(
Trying to figure out what I'm doing wrong here. I want to calculate the number of work days between two dates. My formula: =NETWORKDAY([Start Date1]5, [End Date1]5) It returns 62, but should be more like 86. What am I doing wrong here?
-
Set a default value in a pick list column
I have a column in a project tracking smartsheet that shows the status of a project. I would like the parent row for the project to automatically change depending on the status of the child rows. I can do that through the formula: =IF((COUNTIF(CHILDREN(), "Withdrawn") + (COUNTIF(CHILDREN(), "Requested"))) =…
-
Determine if a date is prior to another date/year - include as criterion
I would like to determine if a date is prior to 01/01/2015 and include it as criterion for the count/distinct/collect combination I inquired about previously?? =COUNT(DISTINCT(COLLECT({CIP Detail Summary Range 4}, {CIP Detail Summary Range 5}, ="OPEN", {CIP Detail Summary Range 3}, =0, IF(YEAR({CIP Detail Summary Range 1}…
-
Find lowest number in Parent/Group
Loving Smartsheet! I need to find the lowest number in a Parent/Child Group. I have multiple Parent/Child Groups. I have (2) helper columns that identifies (1) if it a child in the group, and another that counts the number of children in the group. I believe MIN() will get me the lowest number result...but do not know…
-
Formula not working
I have the follow formula. (See Below) =IF($Status4 = "Close", [ME BUD]4 * $[ME BUD]$2),IF($Status4<>"Close","Open" If $Status4 = "Close" the formula returns a value. However, if $Status4 is not equal to "Close" I want the formula to return "Open". I get the following response #UNPARAEABLE The formula does work if I only…
