Weeknum Within a Range

R

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

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

Teethless mama

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

Entered normally
 
B

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

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

rlm

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top