Count If Current Week

Stradivarian101 · 09/12/18

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 · 09/12/18

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å · 09/13/18

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 · 09/13/18

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 · 09/13/18

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

Paul Newcome · 09/13/18

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 · 09/13/18

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 · 09/14/18

=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å · 09/14/18

Happy to help!

alexis.ray89371 · 08/18/20

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 · 08/18/20

Nevermind, I figured it out! Thanks anyway

Vaughn Stukes · 12/10/20

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

Hasan Syed · 03/05/21

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

How do you make this scale across years?

Paul Newcome · 03/08/21

@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 · 05/13/21

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 · 05/13/21

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

Count If Current Week

Comments

Help Article Resources

Categories