-
INVALID DATA TYPE error
Hi, I'm trying to count the cells that match these criteria but am getting an invalid data type error. =COUNTIFS(OR({Tier}, "Experiencia", {Tier}, "Experiencia Plus", {Tier}, "Experiencia Plus Unlimited"), {Renewal Month}, 1, {Renewal Year}, 2023) The formula worked successfully without the OR statement: =COUNTIFS({Tier},…
-
Countifs from one sheet with multiple categories
I need to complete a countifs formula for data in one sheet with multiple categories (screenshots of data needed). The columns that need to be counted have different drop-down options. I have to count active status defects the total (New, open, Assigned, Ready for Retest, Assigned to Vendor, Reopened) for each module. The…
-
Help with IF/ Then Formula Based on Number of Rows
Hey Guys, I want to create a formula that will return a statement when a number of certain number of rows is reached. I have a unique cell that has a number let's say 10, and I created a helper column (Row ID) that will simply designate each row by a number. I want to create an automatic statement in another cell that will…
-
Julian Date to Short Date
I am trying to write a formula to convert a Julian date to a short date. This formula isn't getting the job done. Any recommendations would greatly be appreciated. *note - this formula does work in excel Julie Date Column: 21123 Short Date Column (text/number type and date type were tried) =IF(0 + (LEFT([Process Julian…
-
Index Match Function Help
Hi Smartsheet Gurus, I am attempt to pull information from one sheet to another given a set criterion that is present in both. I believe that either a VLOOKUP or INDEX function should suffice but cannot seem to get things to work. A little background: We would like to be able to have employees enter a form with data about…
-
If cell matches string criteria in one column, THEN return value of another column (same row)
Hello all, I have the following problem. In my first Smartsheet under the column "Revised Catalog Release" I want to output a date value if a criterion is meet. Now this is the other sheet I am referencing: In lay man words what I want is: If column "Task Name" = "Packaged Part Catalog Release" then please return the value…
-
Dashboard not updating after Sheet changes
For a number of years, our team has been reliant on a PDF Dashboard to issue to our clients. Recently, we have noticed that after new data is input into a Sheet, the Dashboard no longer reflects these changes automatically. Our system is programmed to work in the following manner. (A three-step process -…
-
Formula for workdays based of category and start date
Hey there. I'm working on building on a sheet for the following: I need a formula that generates and order date based off of category and start date. (ie If its a "Pool" category, and construction date of "2/1/23", I need a formula that populates an order date that is 10 business days before the construction date. There…
-
Breakdown of creation date by year and month
Hi, I'm very new to formulas and I've not set up smartsheets previously We currently use a form for external parties to complete, that I then am trying to build a dashboard to have an overview of. I need to show how many forms were completed in both a year in total and also a monthly breakdown. My by year formula is this:…
-
Index Match with multiple ranges?
Hi All, I am stumped on a formula and hoping for some direction. I am attempting to put together an Index(match) that will bring in the "order number" from sheet two to the "Assigned to Contract" column, if the unique identifier appears in any of the "Unique ID columns" (Columns in between hidden for better viewing). Is…