SUMPRODUCT WEEKNUM YEAR

[sam]

Hi everyone,

I have a problem with the above functions. Here is what I have:
A B C
1 7 2010 x

The formula I have is:
=SUMPRODUCT((WEEKNUM(Range1)=$A1)*(YEAR(Range1)=$B1))
=x

The ranges come from another worksheet. I do not want to add a new column in
the raw data worksheet where these ranges are.

Any help would be appreciated and thanks in advance.

Cheers,
Sam.

 

[T. Valko]

WEEKNUM doesn't work on arrays.

Use a helper column to get the WEEKNUM of each individual date then
reference that helper column:

=SUMPRODUCT(--(helper_column=$A1),--(YEAR(Range1)=$B1))