-
Formula Help
Hello Just wondering if someone could help me update this column formula. I need it to exclude the N/A status. It is currently a column formula column that flags any items that are past due from the End Date column. =IF(AND([End Date]@row < TODAY(), NOT(ISBLANK([Program Status Type]@row)), NOT(Status@row = "Complete")), 1,…
-
Count months in another sheet but only within a certain year
I have one sheet with dates listed (ex: 9/10/21) that range from 2020, 2021, and 2022. I'm using this formula to count how many are in each month: =COUNTIF({Head And Neck_Project Overview Range 1}, IFERROR(MONTH(@cell), 0) = 1) But I'd like to break it down by year so I'd like to find how many are in January of 2021 only.…
-
Create SUMIFS formula with a contact criteria in a multiple contacts range
Hi, I am looking for the right way to build my formula that makes a sum of a number column with a contact criteria in a multiple contacts range. Example: =SUMIFS({numberRange with hours}; {Criteria Range with multiple contacts}; Contact Name; {Range with Statuscode}; "In Progress") Check also the screenshot below. If I…
-
Create unique list that populates a second sheet (not report)?
Hello, I'm having trouble figuring out the correct formula to populate a list of unique locations and totals their entries on a second sheet. I couldn't find a forum example that doesn't use a report. This needs to be on a second sheet because it will be used in other formulas and dashboards. Sheet 1 includes a list of…
-
Create multi-contact cell from a row array of individual contacts
I have a sheet as: Header: SME1; SME2; SME3; All SMEs Row 1: Bill Ross; Suzie Collins; Anne Doe; =join([SME1:SME3]@row) Each of the SME columns is a contact. I want to fill my 'All SMEs' column with a formula which makes a multi-contact with all of the contacts joined together for easier filtering. I've tried to write the…
-
Locking cells in sheet
Hello, I am trying to determine if there is a way to lock the formulas I have placed in my sheet so that the users cannot override them to hard key in numbers. The formulas have been entered into all of the necessary cells but as it stands, users can simply clear them out to enter the amounts in their own way. We have…
-
Getting #incorrect argument on IF formula
Help! I had this working then deleted the test lines and it deleted the formula also. Can anyone tell me what I'm missing here? =IF([Request Type]@row = "D&E Contract Signed", ([D&E Amount]@row * 0.3), IF([Request Type]@row = "Project Contract Signed", ([Estimated/Final Project Total]@row * [Estimated/Final Margin]@row *…
-
How to determine if a row id is in the list of predecessors for any row
Hello, I have a fairly large Project plan with 700+ rows and am new to Smartsheet. My predecessors list for any given task has multiple row IDs referenced (comma delimited) With help from Rob Hagan's post (https://community.smartsheet.com/discussion/formula-determine-row-number), I have created the ROW_ID) column for…
-
Vlookup / @row usage
In the Vlookup formula, Category@row references 4 contiguous columns to obtain one value, I added a 5th column and Category@row does not recognize the 5th column. How do I redefine Category@row to use the 5th column ? VLOOKUP(Category@row, {GL Account X Ref Range 4}, 2, false) Thanks for your help! Amy
-
Value count in multi-select column
Does anyone know if on a multi-select column there's a way to count if I only have a single value selected. For example, Based on the data in the "Platform" snapshot, I want to get the counts for second snapshot. Where I'm having the challenge is returning a value for the cells with single value selected that it's not also…