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'm using salesforce connector to pull my team's hours information in real-time. The Salesforce connector sheet contains sheet summaries that I'd like to use a cell reference for a different sheet. I can't seem to find the best way or formula to do this. I don't want to use a dashboard with report widgets because I prefer…
I have two formulas which work well independently, but when I combine them they don't. formula 1: =IF(YEAR([Joined date]@row ) = 2025, JOIN(COLLECT({Membership Survey 2025 - Experience}, {Membership Prioritisation Survey 2025 - Org}, [Organisation name]@row ))) formula 2: =IF(YEAR([Joined date]@row ) < 2025,…
Hello, I use Smartsheets in a Higher Education setting. I have one sheet (Sheet A) for a list of students this sheet contains their student id numbers, email addresses, as well as other helpful information. I built a If Yes/If No check column on this sheet as well. My next sheet (sheet B) is a form that is asking is the…
