-
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?
-
Use Formula to Assign Dropdown Selection in Column
I would like to have the column titled Alert Level return a symbol from the dropdown menu based on the number of selections in the Alert column. I'd like the symbol to be green if there the Alert column for that row is blank. Blue if there is 1 item in the Alert column, Yellow is 2 items and Red if there are 3 or more. Is…
-
How to Pre-Populate a field in a form with a unique ID?
Hello Community, I am asking for your help today figuring out a challenge I am currently facing. We are trying to pre-populate the "Work Order ID" field in the form because it is often overlooked or entered incorrectly. We have been trying to figure it out but it is hard because you cannot have a column formula field in…
-
SUMIFS Returning Zero?
I am trying to sum all hours that meet a certain criteria (match the lookup column/project ID). My Formula: =SUMIF({0 | Affiliate TESS HOURS}, [LOOKUP COLUMN]@row, {0 | Affiliate TESS Lookup}) {0 | Affiliate TESS HOURS} --> references "Entered Hours" {0 | Affiliate TESS Lookup} --> References "Lookup Column" Formula is in…
-
Countif formula for multiple items in parent row
Here is my table: In the Gender ID column I'd like to see a count of M and F in both the 1st and 2nd hierarchy rows. For example, in first row, K, it should end with M-2 and F-0 and the same in the 2nd row, Frizzle. For the row, 4, it should return M-3 and F-3, but under row, Dumbledore showing M-2, F-1 and Yoda M-1, F-2.…
