# Count If Current Week

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.

Tags:
«1

• ✭✭✭

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

• ✭✭✭✭✭✭

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 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 for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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

=WEEKNUMBER(Date@row)

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!

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 for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭

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)

• ✭✭✭✭✭

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)

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