SUMPRODUCT WEEKNUM YEAR
 

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)=$B 1))
=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.

SUMPRODUCT WEEKNUM YEAR
 

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

--
Biff
Microsoft Excel MVP


"sam" wrote in message
...
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)=$B 1))
=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.