WEEKNUM to dynamic range name

[additude]

I need to take a column of dates starting from Jan 1 of the current year
to Dec 31 of the current year (cells from A2 to A366) and assign each
week of that list by the function WEEKNUM and assign it to a dynamic
range name.

I just don't have the Excel experience to know the best way or what the
most efficient options are.

I know I can use the function OFFSET to help, but I'm not sure how to
work the "reference" argument dynamically. For a static example
Week1=OFFSET(A2,7,1) Week2=OFFEST(A9,6,1) Week3=OFFEST(A16,6,1)etc.
works statically for the year 2006, but for the year 2007
Week1=OFFSET(A2,6,1) Week2=OFFSET(A8,7,1) Week3=OFFSET(A15,7,1) etc.

From year to year I need the WEEKNUM to dynamically reference the
correct range of cells associated with it's WEEKNUM.

Ultimately I am using these dynamic range names to sum values in cells
that contain daily sales figures in each department so I can reference
week to week each year.

Thanks.

 

[daddylonglegs]

The Weeknum function won't accept a range argument

The easiest way to do this is to add another column to give the
weeknum, e.g. in Z2 use

=WEEKNUM(A2) where A2 contains your date

You can then sum for a particular week, e.g. week 23 by using a formula
like

=SUMIF(Z2:Z1000,23,M2:M1000)

where column M contains your sales figures

 

[Don Guillett]

try this idea changing the +6 to +13 +20, etc
=OFFSET(Sheet12!$A$1,MATCH(DATE(YEAR(TODAY()),1,1)+6,Sheet12!$A:$A),0,7,1)

 

[additude]

Well,

I believe that was the answer. I had already created the column of
WEEKNUM and basically applied your suggestion of SUMIF and it looks
like it is working.

Thanks!

Can you think of a way to do it without creating the column of
WEEKNUM's? and maybe incorporating it into the SUMIF?

Thanks for your help.

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[daddylonglegs]

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

That's why I said that a column with WEEKNUM was the easiest way...
....although I think you can simplify Bob's suggestion very slightly
to

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