-
Using COUNTIF to determine quantity of rows with number not ending is 000
Hello, I'm trying to setup a "Line Quantity" of children where the primary column contains a 5 digit number. These numbers indicate line items of a purchase order and have a hierarchy themselves. The numbers that are considered the parent end in 000 with all the items that are children end in various numbers in increments…
-
VLookup or not
Im working on a scrum dashboard and want to show the actual sprint name. The Sprint Name (e.g. Sprint 01, Sprint 02...) is in the primary column at Level 2 (task at Level 3 and Release at Level 1). I have a column with the status ("Do", "Doing", "Done") and only one Sprint can be in the "Doing" status. On my metrics sheet…
-
workday calculation with if conditions
Hi, I'm trying to add a workday function to my current calculation. I already use if condition in the cell. Here's what I have. If location is Riverside, then add 3 days to ETA. If location is Memphis, then add 5 days to ETA. IFERROR(IF(Location1 = "RIVERSIDE", ETA1 + 3, IF(Location1 = "MEMPHIS", ETA1 + 5)), 0) However, I…
-
Strip Time When Linking Max Modified (Date) Cell to Column with Restrict to Dates
Hi Smart Sheet Community! I am applying a formula to a cell in our project schedule sheets that I found in a couple posts to grab the MAX value of the Modified (Date) column: =MAX(Modified:Modified) + "" My colleagues and I will be linking the cell with this formula from our project schedule sheets to a "Last Updated"…
-
SUMIF
Hi, I'd like a formula to calculate the below SUM the leave days Request(Column Number of Days) against the employee(Name Column) if Approved(Manager Column) and Approved(EXCO Column). If either one of them are not approved then don't SUM the leave days. This is what i have so far but cant seem to get it to work.…
-
Count checkbox and text in a column
Hi, is it possible to count in a column, if I have check box and also N/A ? I need to count every cell that has a check in the box or the N/A. Thank you !
-
Need Formula to auto populate todays date when box checked
Please share the formula that will auto populate the current date or "Todays Date" in the Completion Date column when the checkbox is populated in the "Done" field.
-
Adding Positive Parents
Hello all, I am trying to set a formula up that adds the sum of several Parent lines, but only if they are positive. Adding all parents under one banner i use the Sum(Children) however, I tried to add only positive parents by using a SumIf(Cell1:Cell5, >0, Cell1:Cell5) but in doing so only managed to add all positive…
-
SUMIF condition
Hi guys! I have to calculate average in my sheet and I will use: SUMIF / COUNTIF - COUNTIF formula is working. - SUMIF formula is not working, i am getting #INVALID OPERATION The SUMIF formula is (screenshot attached) =SUMIF([Assigned to in Region]8:[Assigned to in Region]136, "Sarah Mabiletsa", [Quote Prep Cycle…
-
LOOKUP Formula based on result between date range
Hello, I've tried to create a LOOKUP formula that will provide the "Approved Value" if the "Date Certified" calls between the period stipulated in "Period Start" and "Period End," without much success. Based on the screen-grab, only two values should be returned if the formula is successful. (I've manually entered these to…