-
I NEED HELP GET THE smartsheet equivalent to excel formulas
Formula A =IF((OR(B8="K",B8="PO", B8="", B8="via")),SUMIFS(Contract!R:R,Contract!E:E,C8,Contract!F:F,"0"),"NA") Formula B =IF((OR(B10="K",B10="PO", B10="", B10="via")),SUMIFS(Contract!R:R,Contract!E:E,C10,Contract!F:F,">0"),"NA") Formula C =IF((OR(B9="K",B9="PO", B9="",…
-
VLookUp - #INCORRECT ARGUMENT SET
Hello team! I'm trying to reference all of the columns from one smart sheet (let's call is smartsheet A) to another (smartsheet B). Here is my formula: =VLOOKUP([Helper Column]@row, {Smartsheet A}, 7, true) 7 is the location of the helper column in both smartsheet A and smartsheet B. All of the columns in both smartsheets…
-
How To Collect Data based on date and post to proper month column
Hi All! I have a several large projects running in a portfolio. The projects are all construction in nature and with each project there are subsections of construction each with target completion footage and target completion dates. I would like to build a report to show me the total build footage across the various…
-
Formula to count distinct values in a column that contain a certain text string
I am looking for a way to count distinct values in a column, but only if the values contain a certain text string. For example, in my Fruit column, I'd like to count the unique values that contain "Large." In this case, the number should be 3. Fruit Apple - Small Apple - Medium Apple - Large Orange - Small Orange - Medium…
-
Need Help with #INVALID DATA TYPE Error for COUNTIFS Formula with Multiple Cross-sheet References
Hello, I have this formula: =COUNTIFS({Certifications Range 2}, CONTAINS("CyberArk", @cell), AND({Certifications Range 1}, CONTAINS("Expiring", @cell), CONTAINS("Current", @cell))) I keep getting #INVALID DATA TYPE Range 2 and Range 1 are both referencing Dropdown list columns from another sheet. I know that the problem is…
-
Help with formula
I am working to port a process that is managed in Excel into Smartsheet. I want to created a formula that will check two cells in the row, if they both show filled, I want to check the checkbox. If either or both are any other status, I want the checkbox to remain unchecked. I want to convert this to a column formula. When…
-
Formula to combine Name and email to create a contact row
Hello, I have a very long smartsheet list of names and emails in seperate columns. I want to join them to create a new column that turns them into a contact without having to fill out the contact card for each person. I tried =Join({name}+" "+{email}) and it did join them, but did not turn into a contact. Help please!
-
Stumped on an IF formula
I have data in the following fields: Target Sales: Last Week's Sales: I currently have a formula that tells me if Last Week's Sales are less than Target Sales, then return "Yes" or "No" I need to update the formula to tell me if Last Week's Sales are less than Target Sales, are Last Week's Sales below more than 5% of the…
-
Using a formula to modify the contents of a multi-Contact cell
Hello, Here's the idea: Use a formula that when given a contact cell with multiple assigned contacts, and a secondary cell with one assigned contact, it will SUBTRACT (or otherwise modify) that contact from the first cell. So in practice it would look like this: Cell 1: Contact1, Contact2, Contact3 Cell 2: Contact2 Cell 3:…
-
Count how many times a row is changed to get a percentage
Hello, My goal is to return a percentage of how many times we hit our deliver date the first time. I want to see how many times the deliver date is changed to get a percentage of how many times we hit the first deliver date we put in the field. So to do this I am having the sheet copy the row into a helper sheet and then I…