Sumproduct question

[Guest]

I have the following sumproduct formula that isn't working. Can someone help
me to fix it? It's the weeknum part that I can't get to work.


=SUMPRODUCT(--(IF(WEEKNUM('Raw Data'!$M$2:$M$533,2)=WEEKNUM(N2,2),'Raw
Data'!$M$2:$M$533)),('Raw Data'!$K$2:$K$533))

Thanks,

 

[Biff]

Hi!

Weeknum won't work on arrays.

This looks like what you're trying to do:

=SUMPRODUCT(--(INT((WEEKDAY(DATE(YEAR(M2:M533),1,2-2))+M2:M533-DATE(YEAR(M2:M533),1,-5))/7)=WEEKNUM(N2,2)),--(M2:M533<>""),K2:K533)

Biff

 

[Biff]

Hi!

Weeknum won't work on arrays.

This looks like what you're trying to do:

=SUMPRODUCT(--(INT((WEEKDAY(DATE(YEAR(M2:M533),1,2-2))+M2:M533-DATE(YEAR(M2:M533),1,-5))/7)=WEEKNUM(N2,2)),--(M2:M533<>""),K2:K533)

Biff