Date Function:WEEKNUM

Sam,

Unfortunately, WEEKNUM will not return an array, don't know why, it is just
implemented that way.

This should work for you though

=SUMPRODUCT((1+INT(($A$87:$A$452-(DATE(YEAR($A$87:$A$452),1,2)-WEEKDAY(DATE(
YEAR($A$87:$A$452),1,1))))/7)=A1)*(B$87:B$452))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Metolius Dad" <[email protected]> wrote in message
news:[email protected]...
> Hello Friends
>
> I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452.
> I've got the number of employes I used on that date next to the date in
col
> B. What I want is to have 1-52 in Col A at the top of the sheet and the
> number of employees I used in that week of the year across from the
> corresponding number in Col B. I've tried
> =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452)) but keep getting a
> #VALUE! error.
>
> Thanks for any and all help. I appreciate your being there!
>
> Sam

One way would be to put 01/01/2005 in A1
Put =A1+7 in B1 and drag across for your 52 weeks
Then in A2
=SUMPRODUCT(--($A$87:$A$452>=A1),--($A$87:$A$452<B1),--($B$87:$B$452))
Copy across through B2 etc.

Change the value in A1 to create the correct week for what you regard as
being week 1

--
Regards

Roger Govier


"Metolius Dad" <[email protected]> wrote in message
news:[email protected]...
> Hello Friends
>
> I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452.
> I've got the number of employes I used on that date next to the date in
> col
> B. What I want is to have 1-52 in Col A at the top of the sheet and the
> number of employees I used in that week of the year across from the
> corresponding number in Col B. I've tried
> =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452)) but keep getting a
> #VALUE! error.
>
> Thanks for any and all help. I appreciate your being there!
>
> Sam

Sorry, I have noticed since posting, you asked for the weeks to be in
A1:A52, not across the page
Put 01/01/2005 in A1
In A2 =A1+7
Copy down through A3:A52
Amend formula and place in B1
=SUMPRODUCT(--($A$87:$A$452>=A1),--($A$87:$A$452<A2),--($B$87:$B$452))
Copy down through B2:B52


--
Regards

Roger Govier


"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> One way would be to put 01/01/2005 in A1
> Put =A1+7 in B1 and drag across for your 52 weeks
> Then in A2
> =SUMPRODUCT(--($A$87:$A$452>=A1),--($A$87:$A$452<B1),--($B$87:$B$452))
> Copy across through B2 etc.
>
> Change the value in A1 to create the correct week for what you regard as
> being week 1
>
> --
> Regards
>
> Roger Govier
>
>
> "Metolius Dad" <[email protected]> wrote in message
> news:[email protected]...
>> Hello Friends
>>
>> I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452.
>> I've got the number of employes I used on that date next to the date in
>> col
>> B. What I want is to have 1-52 in Col A at the top of the sheet and the
>> number of employees I used in that week of the year across from the
>> corresponding number in Col B. I've tried
>> =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452)) but keep getting a
>> #VALUE! error.
>>
>> Thanks for any and all help. I appreciate your being there!
>>
>> Sam
>
>

Hello Friends

I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452.
I've got the number of employes I used on that date next to the date in col
B. What I want is to have 1-52 in Col A at the top of the sheet and the
number of employees I used in that week of the year across from the
corresponding number in Col B. I've tried
=SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452)) but keep getting a
#VALUE! error.

Thanks for any and all help. I appreciate your being there!

Sam

Sam,

Unfortunately, WEEKNUM will not return an array, don't know why, it is just
implemented that way.

This should work for you though

=SUMPRODUCT((1+INT(($A$87:$A$452-(DATE(YEAR($A$87:$A$452),1,2)-WEEKDAY(DATE(
YEAR($A$87:$A$452),1,1))))/7)=A1)*(B$87:B$452))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Metolius Dad" <[email protected]> wrote in message
news:[email protected]...
> Hello Friends
>
> I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452.
> I've got the number of employes I used on that date next to the date in
col
> B. What I want is to have 1-52 in Col A at the top of the sheet and the
> number of employees I used in that week of the year across from the
> corresponding number in Col B. I've tried
> =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452)) but keep getting a
> #VALUE! error.
>
> Thanks for any and all help. I appreciate your being there!
>
> Sam

One way would be to put 01/01/2005 in A1
Put =A1+7 in B1 and drag across for your 52 weeks
Then in A2
=SUMPRODUCT(--($A$87:$A$452>=A1),--($A$87:$A$452<B1),--($B$87:$B$452))
Copy across through B2 etc.

Change the value in A1 to create the correct week for what you regard as
being week 1

--
Regards

Roger Govier


"Metolius Dad" <[email protected]> wrote in message
news:[email protected]...
> Hello Friends
>
> I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452.
> I've got the number of employes I used on that date next to the date in
> col
> B. What I want is to have 1-52 in Col A at the top of the sheet and the
> number of employees I used in that week of the year across from the
> corresponding number in Col B. I've tried
> =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452)) but keep getting a
> #VALUE! error.
>
> Thanks for any and all help. I appreciate your being there!
>
> Sam

Sorry, I have noticed since posting, you asked for the weeks to be in
A1:A52, not across the page
Put 01/01/2005 in A1
In A2 =A1+7
Copy down through A3:A52
Amend formula and place in B1
=SUMPRODUCT(--($A$87:$A$452>=A1),--($A$87:$A$452<A2),--($B$87:$B$452))
Copy down through B2:B52


--
Regards

Roger Govier


"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> One way would be to put 01/01/2005 in A1
> Put =A1+7 in B1 and drag across for your 52 weeks
> Then in A2
> =SUMPRODUCT(--($A$87:$A$452>=A1),--($A$87:$A$452<B1),--($B$87:$B$452))
> Copy across through B2 etc.
>
> Change the value in A1 to create the correct week for what you regard as
> being week 1
>
> --
> Regards
>
> Roger Govier
>
>
> "Metolius Dad" <[email protected]> wrote in message
> news:[email protected]...
>> Hello Friends
>>
>> I've got the dates of the year (mm/dd/yyyy) in col A from row 87 to 452.
>> I've got the number of employes I used on that date next to the date in
>> col
>> B. What I want is to have 1-52 in Col A at the top of the sheet and the
>> number of employees I used in that week of the year across from the
>> corresponding number in Col B. I've tried
>> =SUMPRODUCT((WEEKNUM($A$87:$A$452)=A1)*(B$87:B$452)) but keep getting a
>> #VALUE! error.
>>
>> Thanks for any and all help. I appreciate your being there!
>>
>> Sam
>
>