Summing by weeks
 

I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this for
every week, and I'd like to make it more general, so I don't really want to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

one way to do it would be to put in a column such that row 1 would be week
one , row 2 would be week etc
then use sumproduct()
=sumproduct(--(weeknum(A1:A100)=row()),B1:b100)

"jzingman" wrote:

I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this for
every week, and I'd like to make it more general, so I don't really want to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

Maybe I wasn't clear. I have a set of dates in the first column, and I want
to sum the values in the second column that occurred during the first week,
and then those that occurred during the second week, etc. In other words, I
want a weekly sum of the values in the second column. There may be 0, 1 , or
more events each week.

"bj" wrote:

one way to do it would be to put in a column such that row 1 would be week
one , row 2 would be week etc
then use sumproduct()
=sumproduct(--(weeknum(A1:A100)=row()),B1:b100)

"jzingman" wrote:

I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this for
every week, and I'd like to make it more general, so I don't really want to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

=SUMPRODUCT(--(1+INT(($A$2:$A$200-(DATE(YEAR($A$2:$A$200),1,2)
-WEEKDAY(DATE(YEAR($A$2:$A$200),1,1))))/7)=1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jzingman" wrote in message
...
I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this for
every week, and I'd like to make it more general, so I don't really want
to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

=SUMPRODUCT(--(1+INT(($A$2:$A$200-(DATE(YEAR($A$2:$A$200),1,2)
-WEEKDAY(DATE(YEAR($A$2:$A$200),1,1))))/7)=week_num),$B$2:$B$200)

replace week_num by 1, 2 and so on.


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jzingman" wrote in message
...
Maybe I wasn't clear. I have a set of dates in the first column, and I
want
to sum the values in the second column that occurred during the first
week,
and then those that occurred during the second week, etc. In other words,
I
want a weekly sum of the values in the second column. There may be 0, 1 ,
or
more events each week.

"bj" wrote:

one way to do it would be to put in a column such that row 1 would be
week
one , row 2 would be week etc
then use sumproduct()
=sumproduct(--(weeknum(A1:A100)=row()),B1:b100)

"jzingman" wrote:

I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this
for
every week, and I'd like to make it more general, so I don't really
want to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

sumproduct doesn't seem to like weeknum in the argument. This doesn't
evaluate.

"jzingman" wrote:

Maybe I wasn't clear. I have a set of dates in the first column, and I want
to sum the values in the second column that occurred during the first week,
and then those that occurred during the second week, etc. In other words, I
want a weekly sum of the values in the second column. There may be 0, 1 , or
more events each week.

"bj" wrote:

one way to do it would be to put in a column such that row 1 would be week
one , row 2 would be week etc
then use sumproduct()
=sumproduct(--(weeknum(A1:A100)=row()),B1:b100)

"jzingman" wrote:

I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this for
every week, and I'd like to make it more general, so I don't really want to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

Weeknum() works in Sumproduct() for me.
What do you get when you use weeknum by itself?

I should have put the ranges as A$1:A$100 and B$1:B$1100
copying the equation down for 52 cells should give you the weekly totals
with row 1 being the total for week 1, row two for week 2 etc.



"jzingman" wrote:

sumproduct doesn't seem to like weeknum in the argument. This doesn't
evaluate.

"jzingman" wrote:

Maybe I wasn't clear. I have a set of dates in the first column, and I want
to sum the values in the second column that occurred during the first week,
and then those that occurred during the second week, etc. In other words, I
want a weekly sum of the values in the second column. There may be 0, 1 , or
more events each week.

"bj" wrote:

one way to do it would be to put in a column such that row 1 would be week
one , row 2 would be week etc
then use sumproduct()
=sumproduct(--(weeknum(A1:A100)=row()),B1:b100)

"jzingman" wrote:

I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this for
every week, and I'd like to make it more general, so I don't really want to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

Bob:

That one works. Kind of obscure, tho...

Thanks

"Bob Phillips" wrote:

=SUMPRODUCT(--(1+INT(($A$2:$A$200-(DATE(YEAR($A$2:$A$200),1,2)
-WEEKDAY(DATE(YEAR($A$2:$A$200),1,1))))/7)=1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jzingman" wrote in message
...
I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this for
every week, and I'd like to make it more general, so I don't really want
to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

That's because Weeknum doesn't return an array of values, so you have to
hand-craft an equivalent function.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jzingman" wrote in message
...
Bob:

That one works. Kind of obscure, tho...

Thanks

"Bob Phillips" wrote:

=SUMPRODUCT(--(1+INT(($A$2:$A$200-(DATE(YEAR($A$2:$A$200),1,2)
-WEEKDAY(DATE(YEAR($A$2:$A$200),1,1))))/7)=1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"jzingman" wrote in message
...
I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this
for
every week, and I'd like to make it more general, so I don't really
want
to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

Are you sure?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"bj" wrote in message
...
Weeknum() works in Sumproduct() for me.
What do you get when you use weeknum by itself?

I should have put the ranges as A$1:A$100 and B$1:B$1100
copying the equation down for 52 cells should give you the weekly totals
with row 1 being the total for week 1, row two for week 2 etc.



"jzingman" wrote:

sumproduct doesn't seem to like weeknum in the argument. This doesn't
evaluate.

"jzingman" wrote:

Maybe I wasn't clear. I have a set of dates in the first column, and I
want
to sum the values in the second column that occurred during the first
week,
and then those that occurred during the second week, etc. In other
words, I
want a weekly sum of the values in the second column. There may be 0,
1 , or
more events each week.

"bj" wrote:

one way to do it would be to put in a column such that row 1 would be
week
one , row 2 would be week etc
then use sumproduct()
=sumproduct(--(weeknum(A1:A100)=row()),B1:b100)

"jzingman" wrote:

I've seen a few posts along this line, but I haven't figured out a
good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want
this for
every week, and I'd like to make it more general, so I don't really
want to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks

Summing by weeks
 

Hi,

I was hoping to get some help on an extended version of this formula. The
spreadsheet I have records the quantity of e-mails answered each day and I'd
like to sum them by week. The formula below works perfectly for that,
however, the e-mails are for various programs.

I'd like to set up a table that contains a summary of quantity of e-mails by
program for a particular week. The table would have the program names listed
in cells L2:L5.

So, the current formula is
=SUMPRODUCT(--(1+INT(($A$2:$A$200-(DATE(YEAR($A$2:$A$200),1,2)-WEEKDAY(DATE(YEAR($A$2:$A$200),1,1))))/7)=1))

Where $A$2:$A$200 contains the various dates, how do I add to the formula
where $J$2:$J$200 contains the different program information?

Please let me know if any other details are required.

Thanks in advance,
Scott

"Bob Phillips" wrote:

=SUMPRODUCT(--(1+INT(($A$2:$A$200-(DATE(YEAR($A$2:$A$200),1,2)
-WEEKDAY(DATE(YEAR($A$2:$A$200),1,1))))/7)=1))

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"jzingman" wrote in message
...
I've seen a few posts along this line, but I haven't figured out a good
solution. I want to sum by weeks of the year:

=sumif(weeknum(a1:a100), "=1", b1:b100)

But this doesn't work since you can't have weeknum there. I want this for
every week, and I'd like to make it more general, so I don't really want
to do

=sumif(a1:a100, "1/1/2007" AND < "1/8/2007", b1:b100)

for each week of the year.
Is there a general way to do this?

Thanks