Weeknumbers start over every year. So checking the date to see if it's in week
number 2 may not do what you want--unless you really wanted the sum of both
years.
I think I'd use data|pivottable and group those dates by days and then choose 7
for the number of days (in the group dialog).
Or alternatively, you could keep the dates in E1, and C1:C20 and use a formula
like this:
=SUMPRODUCT(--(($C$1:$C$20)>=($E1+1-WEEKDAY($E$1))),
--(($C$1:$C$20)<($E$1+7+1-WEEKDAY($E$1))),
($B$1:$B$20))
(all one cell)
This portion of the formula:
$E1+1-WEEKDAY($E$1)
will return the the date of the previous Sunday (or the date is a Sunday, that
date)
For example, all these dates:
03/19/2006 Sunday
03/20/2006 Monday
03/21/2006 Tuesday
03/22/2006 Wednesday
03/23/2006 Thursday
03/24/2006 Friday
03/25/2006 Saturday
will return 03/19/2006.
This portion will return the next Sunday (7 days more)
$E$1+7+1-WEEKDAY($E$1)
So by typing any date in E1, you're checking to see if the date in C1:C20 is
between those two Sundays.
If it is, it adds the value in B1:B20.
Adjust the ranges to match--but you can't use whole columns.
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html