-
Date formula for Last month, Last Quarter, Last year
I'm looking to add some formula to my summary sheet and the requirement is for analytics for Last month, last quarter and last year - so excluding current month Examples if: current month = May 2020 last month = April 2020 last quarter = February, March & April 2020 last year = May 2019 to April 2020 inclusive These would…
-
How to mark column with 1 or 0 if the month is in a date
Hi, I am trying to track whether or not someone has been active in a month based on their Last Login date. I created the formula to mark a cell as 1 if the month is in the Last Login date or 0 if it is not: =IF(MONTH([Last Login]@row), 1, 0) But the problem is it gives an #INVALID DATA TYPE error if it hits a blank date.…
-
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…
-
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…
-
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…
-
Why am I receiving #INVALID OPERATION error in some of my sheets
I have multiple sheets with the same columns (Health, Task Name, Start, Finish). Note that the Health column is a symbol (red, yellow, green, blue). In my summary sheet, I am using the following Index Match formula to search for Task A in Task Name column and spit out Health. =INDEX(Health:Health, MATCH("Task A", [Task…
-
Numerical "Priority" Method
Hello, I am in need of a method for assigning priority of a submitted row 1-10. If 1 is selected, the previous 1 needs to become a 2, the 2 becomes a 3, etc. The column needs to recalculate when a row is complete and the 'priority' column becomes blank. I don't think this would be possible without using many columns to…
-
ActiveCell References, COLUMN and ROW Functions
I want to make a sheet summary field return whatever is in row 6 of the activecell's column. It has to dynamically change with the cell's position. Similarly, based upon the activecell's row, I want to return the value of Column 2 of that row. Excel has a feature for ActiveCell which then you can use Offset or Index to…
-
Start date of the month for the previous 13 months rolling
I wanted to have a rolling 13 month table that and hoped to have a formula for the first date of the month for 13 months. I have a helper column with today's date in it and have listed the months and the start date as per the attached. Is there a formula that I can put in the start date column to have this roll on from one…
-
Deleting Duplicates by Column Formula
I have a sheet that has rows copied from another sheet. Updated versions of the rows are copied over, and I need to remove the old one. I have added two columns, Duplicate and Tag for Deletion. The formulas are Duplicate - =COUNTIF([Primary Column]$1:[Primary Column]@row, COLLECT([Primary Column]:[Primary Column], [Primary…