Weeknum Within a Range

[rlm]

We are trying to get the number of rows in a range that are in the
same week as a date. The date is in A1 below. The attempted formula is
below. We did use "CTL + SHIFT+ENTER" to create the array. The data is
in the area labeled "DATA!D3:D19".

Is this the wrong approach?

A1 = 2/21/2010
A2 #VALUE!

Formula in A2 {=SUM((WEEKNUM(DATA!D3:D19, 2)=WEEKNUM(A1, 2))*1)}

DATA!D3:D19
---------------------------
3/18/2010 3:19 PM
2/25/2010 3:14 PM
2/26/2010 3:06 PM
3/18/2010 2:53 PM
3/18/2010 2:48 PM
3/18/2010 8:32 AM
2/19/2010 2:12 PM
3/17/2010 2:07 PM
3/17/2010 2:04 PM
3/17/2010 2:03 PM
3/17/2010 2:02 PM
3/17/2010 2:00 PM

[T. Valko]

>Formula in A2 {=SUM((WEEKNUM(DATA!D3:D19, 2)=WEEKNUM(A1, 2))*1)}

WEEKNUM doesn't work on arrays.

You'd have to use a helper column where you get the weeknum for each
individual cell then use that helper column in your formula. For example, in
DATA!E3 and copy down to DATA!E19:

=WEEKNUM(D3,2)

Then:

=COUNTIF(DATA!E3:E19,WEEKNUM(A1, 2))

--
Biff
Microsoft Excel MVP

"rlm" <gro...@rlmoore.net> wrote in message
news:883bf471-8126-4cd9...@z3g2000yqz.googlegroups.com...

[Teethless mama]

A2:
=SUMPRODUCT((INT(DATA!D3:D19)>=A1-WEEKDAY(A1,3))*(INT(DATA!D3:D19)<=A1-WEEKDAY(A1,3)+6))

Entered normally

"rlm" wrote:

> .
>

[Bernd P]

Hello,

As Biff already pointed out, WEEKNUM does not work on arrays.

But you can use INT((x-2)/7) instead of WEEKNUM(x,2):
Array-enter
=SUM(INT((DATA!D3:D19-2)/7)=INT((A1-2)/7))
or enter normally
=SUMPRODUCT(--(INT((DATA!D3:D19-2)/7)=INT((A1-2)/7)))

Please note that this INT formula is not identical to WEEKNUM - it
just maps days of the same week to the same number (as WEEKNUM does
for a different number).

Regards,
Bernd

[T. Valko]

After reading the post again and seeing TM's suggestion, I think TM
interpreted the question correctly.

TM's formula doesn't need the INT functions:

=SUMPRODUCT(--(DATA!D3:D19)>=A1-WEEKDAY(A1,3)),--(DATA!D3:D19)<=A1-WEEKDAY(A1,3)+6))

>But you can use INT((x-2)/7) instead of WEEKNUM(x,2)

Note that that method will not work correctly if using the 1904 date system.

And of course, there's a COUNTIF version which is probably the better of the
methods even though it's a few keystrokes longer.

=COUNTIF(DATA!D3:D19,">="&A1-WEEKDAY(A1,3))-COUNTIF(DATA!D3:D19,">"&A1-WEEKDAY(A1,3)+6)

--
Biff
Microsoft Excel MVP

"Bernd P" <bplu...@gmail.com> wrote in message
news:caf8a606-e76a-491c...@z11g2000yqz.googlegroups.com...

[rlm]

Thanks for the facts and the inspiration. We ended up using COUNTIFS -
very powerful function.