Hi

You are multiplying a vertical array by horizontal arrays, which is why you
are getting an answer of 416
You either need to Transpose the Weekday array,
=SUMPRODUCT(((D1:AH1)-120)*((D1:AH1)-12)*(TRANSPOSE(WEEKDAY(A1+ROW(A1:A31))<6)))
or better still use Column instead of Row to make it horizontal as this
avoids an extra function call
=SUMPRODUCT(((D1:AH1)-120)*((D1:AH1)-12)*(WEEKDAY(A1+COLUMN(A1:AE1))<6))

and you will get the answer of 16

You expect the answer to be 14, because within July, 2 of the 4 Fridays
occur when the result of subtracting 12 from the column value is 1.
However, you are not using the correct data range as you are adding 1 to
July 01 to give 02 Jul as your starting date, and hence to give 01 Aug as
your ending date. Adjust your date range by -1 as shown below
=SUMPRODUCT(((D1:AH1)-120)*((D1:AH1)-12)*(WEEKDAY(A1+COLUMN(A1:AE1)-1)<6))
and your answer will be 14, as expected.

For the sake of consistency, you could use the column range of D1:AH1, but
you would need to subtract an additional 3 from the date, as below
=SUMPRODUCT(((D1:AH1)-120)*((D1:AH1)-12)*(WEEKDAY(A1+COLUMN(D1:AH1)-4)<6))
--
Regards
Roger Govier

"pub" wrote in message
...
what am i doing wrong with my sumproduct()?

A1 = 7/1/2008 (thats July 1st 2008)
D1 = 13
E1 = 11
just to make calculating eazy i followed the 13 and 11 pattern all the
way to AH1
so
F1 = 13
G1 = 11
and so on (eventually these numbers will change randomly)

heres the formula thats failing

=SUMPRODUCT(((D1:AH1)-120)*((D1:AH1)-12)*(WEEKDAY(A1+ROW(A1:A31))<6))

so if the number is greater then 12, i subtract 12...except if i find the
number on a friday(6)

so in this case, i should get 14
but i am getting 416

i can simply so this
=SUMPRODUCT(((D1:AH1)-120)*((D1:AH1)-12))
and i get 16

but i know July 2008 had 2 fridays 7/11/8 and 7/25/8. so they should not
be counted.

evaluate formula looks ok
it has weekday(39630+{1:2:3...:30:31}
then it adds up ok
then it shows the days properly
then it shows the proper number of true Falses
then it just gets wierd

any help? please