Functions that can be used with Sumproduct

[Brad]

I know that the following will wor
=SUMPRODUCT($F$5:$F$370,--(MOD($B$5:$B$370,7)=$S8))/SUMPRODUCT(--($F$5:$F$370>0),--(MOD($B$5:$B$370,7)=$S8))

However
=SUMPRODUCT($F$5:$F$370,--(WEEKNUM($B$5:$B$370)=$S8))/SUMPRODUCT(--($F$5:$F$370>0),--(WEEKNUM($B$5:$B$370)=$S8))

MOD will work but WEEKNUM Will not work - is there a list of functions that
will work (when dealings with vector/arrays) with sumproduct and ones that
will not?

Or do I have to do something special with the weeknum command to be able to
work on something other than one cell?

 

[Dave F]

What error do you get when you try to run the formula?

 

[Brad]

using the - tool - formula auditing funtions give a value error when
evaluating WEEKNUM($B$5:$B$370) - I have the appropriate add-ins included in
Excel

 

[Dave F]

Perhaps WEEKNUM can't be used across a range.

My understanding of its syntax is =WEEKNUM(A1,2)

See this for more info: http://www.cpearson.com/excel/weeknum.htm

As to which formulas can't be used with SUMPRODUCT--I don't know of any such
list.

Dave