Count If Current Week

09/12/18 Edited 12/09/19

Hi Everyone,

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.

Previous1

Comments

  • 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...

    =COUNTIFS(Date14:Date16, =(WEEKNUMBER(TODAY())))

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    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!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    I am going to recommend a helper column (which can be hidden once filled in) called "WeekNumber". In that column put:

     

    =WEEKNUMBER([email protected])

     

    Then reference the WeekNumber column in your COUNTIFS formula:

     

    =COUNTIFS(WeekNumber:WeekNumber, = WEEKNUMBER(TODAY()))

    thinkspi.com

  • That worked! I was trying to avoid adding a "helper" column, but that finally fixed the issue for me.  Thank you!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    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

    thinkspi.com

  • 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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    =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.

    thinkspi.com

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Happy to help!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    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.

  • alexis.ray89371alexis.ray89371 ✭✭✭✭✭

    Hi,

    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)


  • alexis.ray89371alexis.ray89371 ✭✭✭✭✭

    Nevermind, I figured it out! Thanks anyway

  • Is there any way to reference the current week Sunday to Saturday instead of Monday to Sunday?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @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)

    thinkspi.com

  • Hi Paul,

    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.

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Sunny Kaul You are going to need to share permissions across accounts and then "Save as New".

    thinkspi.com

Sign In or Register to comment.