Count If Current Week

Stradivarian101

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.

Sldollman

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å

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

Paul Newcome

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

Sldollman

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

Paul 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

Stradivarian101

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

Andrée Starå

Happy to help!

alexis.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.ray89371

Nevermind, I figured it out! Thanks anyway

Vaughn Stukes

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

Hasan Syed

https://community.smartsheet.com/discussion/comment/100596#Comment_100596

How do you make this scale across years?

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

Sunny Kaul

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 Newcome

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