-
Help needed for SUMIF, Date Range
Hello I am having trouble with the below formula to return date based on a date range and getting a return of #invalid operation. I checked this out in the community but not able to find a resolution. =SUMIFS({All PM PO Tracker-Assigned To}, "Project Manager", {All PM PO Tracker- Change Order-Submitted}, "Invoiced",…
-
Formula that counts how many rows have a date in the past
Hi! I need help with creating a formula that will count how many rows in my column called End Date have dates in the past. I've tried the following and it gives an "unparseable" error: =COUNTIFS([End Date]:[End Date], "<=" & TODAY()) Thanks in advance for your help!
-
MAX Formula Help
I am working on a max formula that I can't seem to figure out how to set up the criteria. Background: I have a sheet (Intake Sheet) that is gathering project health data on a weekly basis via update request. Each week after it is collected, every row gets copied to another sheet (Historical Intake) so always have a…
-
Calculate the # of months between 2 dates, with 1 date being static
Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12/1/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…
-
COUNTIF/SUMIF items with number of workdays between a date and a date range is 3
Hi! I would like to count a number of entries in a sheet if their due date is a certain number of work days before a date in another sheet. I tried to use the following formula: =COUNTIF('reference to a list of entries column in another sheet', NETDAYS('reference to due date column in another sheet', 'cell with a date in a…
-
Formula for returning value based on whether a date is between two dates
=INDEX(COLLECT({Payroll End date Range 6}, {Payroll End date Range 5}, Test@row >= @cell, {Payroll End date Range 7}, Test@row <= @cell), 1) Result: #Circular Reference or =IFERROR(INDEX(COLLECT({Payroll End date Range 6}, {Payroll End date Range 5}, ISDATE(@cell), {Payroll End date Range 7}, ISDATE(@cell), {Payroll End…
-
Looking for VLOOKUP formula to pull data from mulitple sheets
Hi all! I am attempting to use a VLOOKUP formula to pull data across multiple sheets. Since Smartsheet does not have the capability (yet, I hope!) to merge multiple form submissions onto one row, I am creating 2 other data collection sheets and need to pull that data to my master sheet. Each sheet has a unique reference…
-
Input/auto fill data from different rows
Hello, Please find the example photo I would like if there is a formula that can auto fill and input data in blank columns/same row once i type the phone number (in column phone) by calling or looking up for the data from other rows in same sheet (or from other data pase sheet) taht engaged with same phone number. For…
-
Converting Excel Formula to Smartsheets
Hello, Could anyone help me converting this formula into a formula acceptable on Smartsheets please? =IF(H13="","",EDATE(H13,INDEX($P$2:$P$10,MATCH(G13,$O$2:$O$10,0)))+INDEX($Q$2:$Q$10,MATCH(G13,$O$2:$O$10,0))) I keep getting the #UNPARSEABLE error. TIA
-
Fill in project number based on ancestor level
I want to auto fill the project_number between the the Purple and Red rows. So all the children of Purple Project have a Project_Number = 4 and all the Red Children have a Project_number of 7 Tried using something like this. So if the Project is checked return the Project_Number. This give me "UNPARSABLE"…
