Summing by weeks

[Guest]

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

 

[Guest]

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)

 

[Guest]

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.

 

[Bob Phillips]

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

 

[Bob Phillips]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Bob:

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

Thanks

 

[Bob Phillips]

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)

 

[Bob Phillips]

Are you sure?

--
---
HTH

Bob

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

 

[Scopar]

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