-
Ignore #Dividebyzero
Hi, I have a range of percentages and want to calculate the average. There are some #DIVIDEBYZERO cells within the range that will populate later on in the year. Is there a way to tell Smartsheet to ignore the #DIVEBYZERO? It is possible in Excel using an AverageIF formula. But I can't see how to do it in Smartsheet.
-
Countifs + find formula
I have a formula I'm having an issue with. I'm trying to count how many projects are assigned to a individual in the "Assigned To" column that contains a partial description "green Ties" in the "task Name" column. here's my formula I came up with and need help to get it right. =COUNTIFS([Assigned To]1:[Assigned To]160,…
-
Using SUMIF to provide value of orders from this week only
Hi, I am trying to get a formula to sum the total value of orders invoiced this week. The formula i have tried is below, but returns '0' as the value. =SUMIF({Completion Date}, {Completion Date} = WEEKNUMBER(TODAY()), {Order Value}) The sheet references are to single columns titled 'Completion Date' and 'Order Value'…
-
Formula to copy a value to cell which is defined by day of month
Hello! My goal is to create a sheet where I want to store the values which are updated every day during sync with Jira. And I want to copy a value to a cell that is corresponds to current day of week. The result should be something like on the screenshot. So I need some kind of formula: value from Jira copied every day to…
-
#INVALID REF
Below formula works fine in some cells but in others it says invalid ref in the same column. What could be the issue? =IF([JC Number]11 = "", "", COUNTIFS({JC number}, [JC Number]11, {SCT}, "Yes", {Trucks Status}, OR({Truck Status} = "Road Freight - Moving Loaded", {Truck Status} = "Road Freight - Import Clearance", {Truck…
-
COUNTIF or COUNTIFS
I think I have formulated myself into a corner. What I am attempting to do is create a total number of all open projects based on the multiple status options in 5 separate columns. So, if the data in columns Stats1:Stats5 match X or Y or X, return that total count. I am able to do this for One…
-
Formula(s) for calculating past due items on a construction punch list.
I'm creating a simple punch list template and would like to add some date calculating functions to it but I'm very unfamiliar with creating date formulas. I'm attaching a screen shot of this rough template with a sample line already entered. I would like to create a formula that will tell me how many days past due items…
-
Formula
I am trying to use the below formula to have an average of answers but it is not working can anyone check the formula and let me know what is wrong =((COUNTIF(CHILDREN(), "Excellent") * 5) + ((COUNTIF(CHILDREN(), "Very Good") * 4) + (COUNTIF(CHILDREN(), "Good") * 3) + (COUNTIF(CHILDREN(), "Fair") * 2) +…
-
Pull a calendar
Hi-- so, I have a series of columns representing various tasks. And then I have a series of rows representing various projects. I enter various dates into the corresponding cells (eg Project X and Task Y will occur on such and such date). What I want to do then is on a separate sheet list all of the dates in the year and…
-
Connect Action log (dates) with a project sheet dates
Hello; I am currently working with two sheets, one is the project plan and the second one with an action log corresponding to one of the phases of the plan. I have an issue when I am trying to connect log date & due date of each of the actions of the action log, with the finish date of some of the activities from the…
