ExcelBanter - Weekly count.

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Weekly count. (https://www.excelbanter.com/excel-discussion-misc-queries/178202-weekly-count.html)

Hello,

I have a sheet in which I have the rows labeled from 1 to 31 for each day of
the month, and several columns labeled as intakes, exits etc.

my problems is that I need a *weekly* count of those categories, and each
month does not exactly start on a monday nor that monday being the 1st of the
month. :-)

Each month I start the sheet from a not filled, but formatted and protected
sheet. where all I have to do is change the appropriate cell to reflect the
month and year in course.

Can anyone help me with suggestions as I have been doing this manually and I
have the hunch it can be done easier.

I use Office 2003.

TIA

Hernan

Say you have the dates in column A and some data in column C. In B1 enter:

=WEEKNUM(A1) and copy down. Here is an example for February 2008:

2/1/2008 5 18
2/2/2008 5 47
2/3/2008 6 46
2/4/2008 6 30
2/5/2008 6 19
2/6/2008 6 44
2/7/2008 6 27
2/8/2008 6 39
2/9/2008 6 37
2/10/2008 7 47
2/11/2008 7 21
2/12/2008 7 11
2/13/2008 7 17
2/14/2008 7 38
2/15/2008 7 29
2/16/2008 7 39
2/17/2008 8 13
2/18/2008 8 19
2/19/2008 8 25
2/20/2008 8 15
2/21/2008 8 49
2/22/2008 8 34
2/23/2008 8 30
2/24/2008 9 26
2/25/2008 9 43
2/26/2008 9 37
2/27/2008 9 34
2/28/2008 9 35
2/29/2008 9 24

since the weeknumber is explicitly available as a column, to calculate the
sum of column C for week #8, use:

=SUMPRODUCT(--(B1:B44=8),(C1:C44))

--
Gary''s Student - gsnu200771

"Hernan" wrote:

Hello,

I have a sheet in which I have the rows labeled from 1 to 31 for each day of
the month, and several columns labeled as intakes, exits etc.

my problems is that I need a *weekly* count of those categories, and each
month does not exactly start on a monday nor that monday being the 1st of the
month. :-)

Each month I start the sheet from a not filled, but formatted and protected
sheet. where all I have to do is change the appropriate cell to reflect the
month and year in course.

Can anyone help me with suggestions as I have been doing this manually and I
have the hunch it can be done easier.

I use Office 2003.

TIA

Hernan

Thank you for replying. However, I don't understand what you are proposing.
Maybe I was not explicit enough or simply I do not have the savvy that you
are sporting. :-)

Anyway this is how my sheet looks: (The title and Day are in column A)

------------------------------------------------
Report for the month of Feb 2008
------------------------------------------------

Day|Intakes|exits| DNS|
totals
1 | 2 | 3 | 1 | |
Intakes | Exits | DNS |
2 | 1 | 1 | 0 | week1 |
| | |
3 | 4 | 0 | 0 | week2 |
| | |
etc until day 31. week3 |
| | |
At the end I have the totals per column week4 | |
| |
week5 |
| | |

So I need totals for week 1 for intakes, exits and dns. Depending on what
day of the week is the 1st of the month is what the totals for the weeks will
be, as the first week could have only one day. Right? Hence I have 5 weeks
listed.

Is this more understandable? or maybe this question needs to be made in the
programming forum? I will post there if I am directed to do so.

TIA

Hernan

"Gary''s Student" wrote:

Say you have the dates in column A and some data in column C. In B1 enter:

=WEEKNUM(A1) and copy down. Here is an example for February 2008:

2/1/2008 5 18
2/2/2008 5 47
2/3/2008 6 46
2/4/2008 6 30
2/5/2008 6 19
2/6/2008 6 44
2/7/2008 6 27
2/8/2008 6 39
2/9/2008 6 37
2/10/2008 7 47
2/11/2008 7 21
2/12/2008 7 11
2/13/2008 7 17
2/14/2008 7 38
2/15/2008 7 29
2/16/2008 7 39
2/17/2008 8 13
2/18/2008 8 19
2/19/2008 8 25
2/20/2008 8 15
2/21/2008 8 49
2/22/2008 8 34
2/23/2008 8 30
2/24/2008 9 26
2/25/2008 9 43
2/26/2008 9 37
2/27/2008 9 34
2/28/2008 9 35
2/29/2008 9 24

since the weeknumber is explicitly available as a column, to calculate the
sum of column C for week #8, use:

=SUMPRODUCT(--(B1:B44=8),(C1:C44))

--
Gary''s Student - gsnu200771

"Hernan" wrote:

Hello,

I have a sheet in which I have the rows labeled from 1 to 31 for each day of
the month, and several columns labeled as intakes, exits etc.

my problems is that I need a *weekly* count of those categories, and each
month does not exactly start on a monday nor that monday being the 1st of the
month. :-)

Each month I start the sheet from a not filled, but formatted and protected
sheet. where all I have to do is change the appropriate cell to reflect the
month and year in course.

Can anyone help me with suggestions as I have been doing this manually and I
have the hunch it can be done easier.

I use Office 2003.

TIA

Hernan

Replying to myself here... :/

Sorry for that formatting. I will have to reformulate my question with a
better formatted example.

"Hernan" wrote:

Thank you for replying. However, I don't understand what you are proposing.
Maybe I was not explicit enough or simply I do not have the savvy that you
are sporting. :-)

Anyway this is how my sheet looks: (The title and Day are in column A)

------------------------------------------------
Report for the month of Feb 2008
------------------------------------------------

Day|Intakes|exits| DNS|
totals
1 | 2 | 3 | 1 | |
Intakes | Exits | DNS |
2 | 1 | 1 | 0 | week1 |
| | |
3 | 4 | 0 | 0 | week2 |
| | |
etc until day 31. week3 |
| | |
At the end I have the totals per column week4 | |
| |
week5 |
| | |

So I need totals for week 1 for intakes, exits and dns. Depending on what
day of the week is the 1st of the month is what the totals for the weeks will
be, as the first week could have only one day. Right? Hence I have 5 weeks
listed.

Is this more understandable? or maybe this question needs to be made in the
programming forum? I will post there if I am directed to do so.

TIA

Hernan

"Gary''s Student" wrote:

Say you have the dates in column A and some data in column C. In B1 enter:

=WEEKNUM(A1) and copy down. Here is an example for February 2008:

2/1/2008 5 18
2/2/2008 5 47
2/3/2008 6 46
2/4/2008 6 30
2/5/2008 6 19
2/6/2008 6 44
2/7/2008 6 27
2/8/2008 6 39
2/9/2008 6 37
2/10/2008 7 47
2/11/2008 7 21
2/12/2008 7 11
2/13/2008 7 17
2/14/2008 7 38
2/15/2008 7 29
2/16/2008 7 39
2/17/2008 8 13
2/18/2008 8 19
2/19/2008 8 25
2/20/2008 8 15
2/21/2008 8 49
2/22/2008 8 34
2/23/2008 8 30
2/24/2008 9 26
2/25/2008 9 43
2/26/2008 9 37
2/27/2008 9 34
2/28/2008 9 35
2/29/2008 9 24

since the weeknumber is explicitly available as a column, to calculate the
sum of column C for week #8, use:

=SUMPRODUCT(--(B1:B44=8),(C1:C44))

--
Gary''s Student - gsnu200771

"Hernan" wrote:

Hello,

I have a sheet in which I have the rows labeled from 1 to 31 for each day of
the month, and several columns labeled as intakes, exits etc.

my problems is that I need a *weekly* count of those categories, and each
month does not exactly start on a monday nor that monday being the 1st of the
month. :-)

Each month I start the sheet from a not filled, but formatted and protected
sheet. where all I have to do is change the appropriate cell to reflect the
month and year in course.

Can anyone help me with suggestions as I have been doing this manually and I
have the hunch it can be done easier.

I use Office 2003.

TIA

Hernan

I cannot download it.

Thanks anyway.

Hernan

"Herbert Seidenberg" wrote:

Or use Pivot Table
http://www.freefilehosting.net/download/3clke