-
How to prevent Countifs column formula from updating previous cells.
I have created a log book request form that needs to generate a unique ID (YYYY-##-###) based on 3 criteria: Year submitted (4 digit year), Site Code (preassigned 2 digit site code), and Log Book Type request count. The formula I'm using performs as needed, except it changes previous Log book numbers that have rows with…
-
Counting # rows submitted in a particular month and year
Hello, my current formula only counts what month a row was submitted but not the year. How can I get the formula to also count the row if it happened in January of the current year? =COUNTIF({Date Submitted}, IFERROR(MONTH(@cell), 0) = 1)
-
SUMIFS Help
Hi all, Having an issue with a formula; I want to sum column 3 based on fulfilling criteria from 2 other columns eg If Column A says "Product" and column B says "2024 May" then sum column Column C It may seem simple, but having issues... Thanks
-
Remove Accented characters and replace with non-accented characters
Hello, I have an intake of name data in three columns: First Name, Middle Name and Last Name. I have then got a formula to join them up in a fourth columns: E.g., Selma Anna Żakowicz will become: Żakowicz/Selma Anna Joined Up Formula: =[Last Name]@row + "/" + [First Name]@row + " " + [Middle Name]@row This joined up column…
-
NEW Help Article! Automatically update the TODAY function in formulas
Hi Community, Do you use the TODAY Function in any of your formulas? If so, there are a few different ways you can make sure this function is updated daily to reflect the current date without needing to open the sheet. Check out this NEW Help Article titled Automatically update the TODAY function in formulas that details…
-
IF/THEN/Else with Dates
Hi All! I'm needing to create a column date formula for if appointment type = a or b, then Report Due Date = Appt Date + 60; and if Appointment Type = anything else, then report due date = appt date + 30. Based on answers to other similar questions, here is what I have so far: =IF(APPOINTMENT_TYPE@ROW="SIBTF_-_INITIAL",…
-
Auto checkbox formula when cell contains multiple values
Hello! I am trying to create a formula that automatically check the box if the "Year upgrade or addition" cell contains the year 2012 or later. In my screenshot, you can see this cell contains the years 2007 and 2016. Since 2016 matches my criteria, I need the ADA Complaint? column to be checked. I tried =IF([Year upgrade…
-
Find Unassigned Team with JOIN(COLLECT(
[Allocated Team] is the team that the project has said they need and at what allocations they need them, JOIN(COLLECTed from the children of this parent row =JOIN(COLLECT(DESCENDANTS([Employee Name]@row), DESCENDANTS([Start Date]@row), @cell < TODAY(), DESCENDANTS([End Date]@row), @cell > TODAY()), CHAR(10)). [Assigned…
-
IF Formula to give me a $ amount when criteria's are met
I am working on a calculation model for my company and essentially I am looking for Smartsheet to give me the following information. I currently have the formula for the first part figured out, I need help with the 2nd part. If the "Quantity is <= 10 the amount should come out to $13,500.00 I need this formula to multiply…
-
SUMIF Using criteria from the Sheet Summary
Hi, I have a sheet that captures hours from a vendor. Hours and rework counts (number of times an issue goes into rework) are entered. I need to sum hours and rework counts for the previous month. I have a calculation in the sheet summary the takes the year and month and forms a YYYY-MM field. I am comparing this sheet…