-
SUMIF stopped working
This formula was working in my sheet (summary field) and it just stopped. I don't know what I could have changed to make this stop working. I am now getting an invalid data type error. It's a simple formula! If the year is 2022, sum up the charges column. My Year column is calculated, YEAR([Date Signed]), is that why? I…
-
Year Support Contracts - Automation to update end date
We use the following formula to populate the end date of a one year contract. Namely if it starts the 1st Oct 2022 the contract expires on the 30th Sept 2023. This formula works for every other day in the month except when the renewal falls on the 1st ? =IFERROR(DATE(YEAR([Support Contract Start Date]@row) + 1,…
-
How to include Count Collect formula with Parent Row Count Formula?
Hey there, I'm trying to add a 'Count Parent and other rows' formula to my existing count collect formula. See existing formula below. =COUNT(COLLECT({Row ID}, {Category}, $Label@row)) It is counting specific fields from the intake sheet and placing in a newly created sheet. To update it I want to exclude child rows from…
-
Can you create a column formula for auto-number generation that will recognize ancestor relationship
I have a sheet that I would like to custom create an auto-number formula so a record ID is given when a new record is submitted, but I also also want it to be able to recognize descendant rows and give them the same Record ID as the ancestor row (instead of generating a new record ID for every new row). Below is an example…
-
How do I count by year based off a condition?
Hello, I'm trying to count how many dates in each state per year (snapshot below). I've tried this formula but it's not counting correctly: =COUNTIFS({State/country}, @cell = "NSW", {Date 6th Day was worked:}, AND(IFERROR(YEAR(@cell), 0) - 2021)) What do I need to change?
-
Month to Date and Year to Date spending
I have a sheet which contains date and amount spent, among other things. I want to show a MTD and a YTD metric on a dashboard. I am assuming I need to make a new sheet with a formula pulling data from the original sheet. I would like this to be ongoing as the original sheet is updated and then at the start of a new…
-
WBS Formula Question
I was hoping to get some help with a question regarding the WBS template from SmartSheet I found this template very helpful, and I would like to use it for my projects, but I need a little assistance with modifying the formula. I am not sure if this same setup can be used when the project tasks don't start on the first…
-
Help turning tasks yellow less than 30 days till finish
I have the R/G ok, blue for complete, but I cant seem to get it yellow if the finish date is less than 30 days away: =IF(Status@row = "complete", "Blue", IF(Finish@row < TODAY(), "Red", IF(Status@row = "In Progress", "Green")))
-
Max(Collect()) To pull in the most recent entry
Hi! I am attempting to use MAX(COLLECT()) to pull in the most recent entry from a manually maintain status log. If the date in "Week off" column on the log is the most recent date and the Project name matches the name on the row in the "Data Sheet" then I want to populate the entries in the "Weekly Highlight" column.…
-
COUNTIF Formula that counts cells with multiple drop-down values
Can this be done?
