I have some daily data with dates and I want to get the week to date numbers off that data (Monday - Sunday of current week). I know countif is needed but I'm not sure how to go thru the rest.
Your help is appreciated.
I'm interested in finding the same solution.
I came up with this for a formula, but the result is 0, even though 2 of the 3 dates in that range are this week. Not sure what I'm missing...
Hi Stradivarian101 & Sidollman,
Try this. (I think that you want to use the SUMIF and not the COUNTIF)
=SUMIF(Date:Date; WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()); Daily:Daily)
Depending on your country you’ll need to exchange the semi-colon to a comma in the above formula.
I hope this helps you!
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
I am going to recommend a helper column (which can be hidden once filled in) called "WeekNumber". In that column put:
Then reference the WeekNumber column in your COUNTIFS formula:
=COUNTIFS(WeekNumber:WeekNumber, = WEEKNUMBER(TODAY()))
That worked! I was trying to avoid adding a "helper" column, but that finally fixed the issue for me. Thank you!
I used to try to avoid them as well, but I have since found they make life in Smartsheet SO much easier. I lock them, shove them to the far right of the sheet, then "Hide" them. No one knows they're even there. They just think it's magic. Haha
Thank you. One problem that I encountered though is if the range of dates have a blank cell, it returns an error. Is there any way to fix it since some cells have blanks on my column.
=SUMIF(Date:Date; IFERROR(WEEKNUMBER(@cell), 0) = WEEKNUMBER(TODAY()); Daily:Daily)
You could have an IFERROR statement replace any problems (blanks) with a zero which won't be counted since there technically is no week number of 0. The SUMIF is just looking for the numbers to compare, not whether it is a valid week number or not, so filling with a zero (not a week number of zero) should do the trick.
Happy to help!
I have a similar question. I added the helper column for Week Number. I am trying to return a checked box if the submitted date falls in the current week. I have the below formula that returns an unparseable error. Are you able to help with what I am doing incorrectly? Thanks!
=IF([Week Number]:[Week Number] = [Week Number]1(TODAY()), 1,0)
Nevermind, I figured it out! Thanks anyway
Is there any way to reference the current week Sunday to Saturday instead of Monday to Sunday?
How do you make this scale across years?
@Hasan Syed Try something like this to incorporate the current year...
=SUMIF(Date:Date; AND(IFERROR(WEEKNUMBER(@cell); 0) = WEEKNUMBER(TODAY()); IFERROR(YEAR(@cell), 0) = YEAR(TODAY())); Daily:Daily)
Hope you are doing awesome!
I'm back again with another query...
I have one Smartsheet account with one of my clients. And I have created multiple Workspaces with Sheets and Reports. I want to use those as templates for my other client with a separate Smartsheet account.
These 2 Smartsheet accounts are with different user ID's. I don't want to share permissions. Instead, can I copy Sheets and Reports (with formulas & formats) from one Smartsheet account to another?
I'm trying to achieve my goal of saving a lot of time as I can avoid creating those multiple formulas again in the New Smartsheet.
Thanks a ton in advance.
@Sunny Kaul You are going to need to share permissions across accounts and then "Save as New".
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
©2022. All Rights Reserved Smartsheet Inc.