-
Help with formula...
Hello everyone, I am trying to create a formula that could capture two parameters. The following grid aims to show the total approved change order amount by Vendor Name AND Original SOW#. For instance, the following example shows 2 different vendors (Apple and Watermelon). The total approved amount for Apple should be…
-
Formula or condition to eliminate the number "0"
Hi i am trying to build a formula in Smartsheet that make count from a cell and including other items from other. The question is how to eliminate the number zero if the count is zero? My formula it is working perfectly fine with the count but I cannot eliminate the zero. =COUNTIFS({Client Sector}, "Commercial",…
-
Cannot get total number of checked boxes to work
I have tried every formula i could find here, but i cannot get it to count how many checkboxes are checked in a column. The checkboxes are in Column "Check" and go from row 1-99" i am putting the formula in row 100 (so directly below the checkboxes). Here is the formula i have narrowed it down to, but still doesnt work:…
-
Set Start and End Date for Two Tasks
I'm trying to find a solution for populating a start and estimated completion date for two tasks simultaneously. For example, I use headings for Start Date, Est. Completion, and Completed. I have a task (Validate Scope) that should mirror the Start Date and Est. Completion of the Execution task (parent row). I tried using…
-
Index Collect return a value depending on value of a cell
I am trying to collect a value depending on the choice in a cell. I have 2 sheets using the following columns: Incoming Issues Site ID (Text field) Division (Dropdown field with 2 options: External, Internal) Site Name (Text field) Information Sheet Internal Site ID (Text field) External Site ID (Text field) Site Name…
-
Join Function Returning Several blank values
I have read a ton of other posts on this, but none of the solutions are working on my issue. I have several other sheets working together, and I am attempting to join the values on one sheet based on an if statement in another. Here is the formula that returns the blank values: =JOIN({Home Church Kids Volunteers Range 2},…
-
Regarding % Completion Formula
I am having a rather confusing issue. For my sheet referenced below - I am attempting to calculate the percent completion using this formula: =MIN((TODAY() - Start@row) / ([Actual Finish]@row - [Actual Start]@row), 1) When i insert it in Column 36 below it works.. but when place the same formula in the 5 Complete column…
-
Looking for a formula that will auto increment number every day based on other cells assigned values
Essentially I am looking to count days and issue is open and assigned to our company. I was able to somewhat achieve this using a second sheet but there is an administration overhead to this. Issues were copied to second sheet every night and then column in main sheet incremented the days with Team column based on current…
-
Quantity AND % Complete for children rows
I'm trying to make the parent row say the qty of children AND the % in the "Complete" column that will auto adjust once checkboxes are clicked. Note, each parent row will have different quantities of children. Right now I am using the below formula and I don't like that it gives so many decimals: =COUNTIF(CHILDREN(), true)…
-
Return a count based on date range from reference sheet
I have created a summary sheet to get a count of how many times "Foundation/Foundational Analytics/Health Insights/Type 1 Audit" shows up in a column, referencing another sheet using this formula.. "=COUNTIF({Charter WAE AS-S Action Item List Range 1}, "Foundation/Foundational Analytics/Health Insights/Type 1 Audit") In…
