-
How do I count 2023 Q1 items that are not specific request statuses?
Here is my formula: =COUNTIFS([Plan of Record]:[Plan of Record], FIND("2023", @cell) > 0, [Request Status]:[Request Status], NOT(CONTAINS("Cancelled", @cell), NOT(CONTAINS("Merged", @cell), NOT(CONTAINS("Rejected - Insufficient Value", @cell), NOT(CONTAINS("Rejected - Duplicate", @cell)), [Planned MVP Launch Date]:[Planned…
-
INDEX, DISTINCT, and MATCH formulas
I am trying to put together a list of our project managers and which business units each of them are responsible for. I need it to be in a certain format because right next to each of the business units, I am going to have 3 COUNTIFS formulas to count different things based on the project manager and business unit. I…
-
Collect and display associated numbers
Hello, I'm looking for a formula to use to reference another sheet and collect and display the different User Story numbers associated with and OMNI #. I was thinking maybe VLOOKUP or INDEX with COLLECT or MATCH but I can't get it to work. Here is the data set: So if the formula works correctly, the row with OMNI002800…
-
Kanban-grid to Milestone date/calendar week | find latest column with same text
Hi Everyone, we build a pretty big sheet to track and work a bigger project. Therefore we have 3 milestones per task. One task per row, except mother rows with titles for organization. For visualisation the calendar weeks set in the 3 milestone columns were shown with colour at the right side of the sheet in a self build…
-
Calculating elapsed time in percentage #UNPARSEABLE
I wrote the following formula to get the percentage according to the chart based on today's date, but it didn't work. Please Help =IF(OR(ISBLANK([Start date]@row); ISBLANK([End date]@row); ISBLANK(TODAY())); "";IF(TODAY() < [Start date]@row; "Not Yet";IF(TODAY() > [End date]@row; "100%";TEXT((TODAY() - [Start date]@row +…
-
Calculating percentage of check boxes checked in a column
Hi, I am using the below formula within the sheet summary section to calculate the percentage of check boxes checked in the Completed column. I am getting an incorrect percentage though as the formula is taking into account the cells that have no empty check boxes (no text in the rows). =COUNTIF(Completed:Completed, 1) /…
-
Automation Based off a Formula Column Not Working
Hello! I have set up the automation below: The Column that triggers the automation "SKU Created in NS" is an Index/Match formula. =INDEX({PO Request Form Range 1}, MATCH(Project@row, {PO Request Form Range 2}, 0)) My automation is not working when the box changes to "checked" based on activity in the other sheet. Is this a…
-
#INCORRECT ARGUMENT SET
Good Morning, I need to sum all executed contracts, but only summing the most recent record. I am using SUMIFS and my formula isn't working out. =SUMIFS({Sheet - Contract Management - Complete Lib Range 3}, "Executed", {Sheet - Contract Management - Complete Lib Range 4, "Y",{Sheet - Contract Management - Complete Lib…
-
Compare today's date with existing Date
Hi I'm trying to create a formula that compares an Expiring contract date to today's date. If today's date is greater (past) the Expiring contract date the status will flip to "Expire". Otherwise it's "Active" This is the formula I wrote but is giving me a #parseable error =IF(TODAY() - [Contract Expiration Date]@row) > 1,…
-
Sheet Summary not matching actual with multiple criteria
Hello, I am new to smartsheet formulas and have a problem that needs to be solved. I am trying to capture a count of sites that have 2 criteria. Completed in one column and counting if the dropdown is "Full Install" "Pre Cabled UPS Only" and "Revisit". On my sheet summary, I get a total of 14 using the below formula but my…
