Sign in to join the conversation:
I'm trying to total up the job value column based on weekly totals. Can I have the formula calculate the weeknumber or do I have to create a helper column?
I am now running into the issue where my formula is counting weeknumbers over 2018 and 2019. How would I fix this in this formula to just pick up the weeknumbers in 2019?
=SUMIFS({MASTER SCHEDULE Range 4}, {MASTER SCHEDULE Range 5}, IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = 36)
We would simply use an AND statement to include a YEAR function in the criteria.
=SUMIFS({MASTER SCHEDULE Range 4}, {MASTER SCHEDULE Range 5}, AND(IFERROR(WEEKNUMBER(DATEONLY(@cell)), 0) = 36, IFERROR(YEAR(DATEONLY(@cell)), 0) = 2019))
It's still totaling week numbers that are future.. see attached
I am not sure what you mean...
The formula above will sum all cells in range 4 who's date in range 5 falls within week number 25 of the year 2019.
What is the desired outcome?
week 38 is 9/15
week 39 is 9/22
Neither of those dates are in the range for 2019.
We enter those dates as we get retainers. it should be zero until we receive a retainer and someone records it.
You would need to include that logic in your SUMIFS formula.
I have a parent row that I'm using to sum all child row values where Children = 0 and Status = "Not Started". This is my formula. =IF(OR(ISBLANK(Status@row ), Hierarchy@row = 0), "", IF(Hierarchy@row = 1, SUM(CHILDREN()), IF(AND(Children@row = 0, Status@row = "Not Started"), 1, 0))) However, if any of my task rows are…
Hi, I am trying to get a column that provides the Date (easy with Record a Date) but I need the TIME as well. Most of my tables I just use the right function on the Modified Date because the only thing updating those tables are automations or data imports. But a few tables have automations, data imports, and manual inputs.…
I’m hoping to get a second set of eyes from the community in case I’m missing something obvious or there’s a cleaner pattern I should be using. I’ve used ChatGPT to try and help me group/organize my situation coherently…. Because at this point I feel crazy… I’ve literally worked on this for hours. Environment •Smartsheet…
