-
Tardy Identification System
I am able to flag down if the 'Date Completed' surpassed the 'Deadline'; however, I am unsuccessful in making a tiered tardy system: Ontime, Late (+30 days late), and Super Late (+60 days late). =IF([Date Completed]@row - Deadline@row > 0, "LATE", IF([Date Completed]@row - Deadline@row <= 0, "ON TIME", IF([Date…
-
distinct formula not working
Hello I'm trying to count how many cases does a subcontractor have in warranty. See, a case can have multiple reclamations and in the source that I'm using, cases are duplicated with different reclamation IDs (waterproofing, electrical, etc). This is the formula I'm trying to use but it does not work. I've also tried to…
-
Formula for counting how many people have signed in each month, referencing another sheet?
I am trying to count how many people have signed in for each month referencing another sheet so that the number appears on my metrics sheet. The below is what I have at the moment but it isn't working: Trying to find how many people signed into Yard A in January 2023: =COUNTIFS({Yard A Date}, IFERROR(MONTH(@cell), 0) = 1,…
-
COUNTIFS formula issue. Help Please?
Greetings! I am trying to count the number of times a contact appears in a range on a sheet when certain conditions apply. I am trying to make this a column formula instead of a row formula so I don't have to input every individual's name into a formula for what will end up being 20 columns of counting. The range to search…
-
New to Smartsheets and Formulas
Hello! I currently have this formula in my status column =IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress")) but I'd like to add a rule that if the finish date is < today, "Overdue". Can somebody help me complete the formula? Also, I'm looking for recommendations for training. I'm…
-
Trying to get dates to populate off formulas
Essentially, I need to have users manually enter a start date (this could be either one cell or I could dedicate a column if needed), and based on the needed duration for each tasks the start and end date columns would be generated. For the Duration column, I would like this to populate via lookup table as the durations…
-
Join Collect with multiple criteria
Can someone help me with this formula? It will not work for the life of me. =JOIN(COLLECT({Current Loans Range 5}, {Current Loans Range 1}, "CASA"), [{Current Loans Range 2}, "Y"]), "; ")
-
Need help with a formula
=COUNTIFS({Demand State}, [Primary Column]@row, {Investment Class}, "Grow", {Portfolio}, "P&C Claims", <=DATE(2023, 1, 1), {Demand Opened Date}, >=DATE(2023, 12, 31)) Can someone help me figure out why it's not working?
-
MAX COLLECT WITH DATES
Hello, I'm hoping someone can help me with this. I need to pull the latest date from the "Active - Time Stamp ON" column based on the DNIS being the same. Seeing the below screenshot, the first 5 rows have the same DNIS of 0012. So the date I need it to pull back is the latest date of 06/13/2022 (from row 5). I have tried…
-
Combination of CountIFS and OR
I am trying to accomplish this: Count If: If {Current Development - Initiative}, ="Concur HS4" And, If {Current Development - Hierarchy}, ="EPIC" And If either one of these columns is not Empty {Current Development - Q2'23} <> "", {Current Development - Q3'23} <> "" This is the formula I used, but it shows #Invalid…
