Functions that can be used with Sumproduct
 

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

However
=SUMPRODUCT($F$5:$F$370,--(WEEKNUM($B$5:$B$370)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(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?

Functions that can be used with Sumproduct
 

What error do you get when you try to run the formula?
--
Brevity is the soul of wit.


"Brad" wrote:


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

However
=SUMPRODUCT($F$5:$F$370,--(WEEKNUM($B$5:$B$370)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(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?

Functions that can be used with Sumproduct
 

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" wrote:

What error do you get when you try to run the formula?
--
Brevity is the soul of wit.


"Brad" wrote:


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

However
=SUMPRODUCT($F$5:$F$370,--(WEEKNUM($B$5:$B$370)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(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?

Functions that can be used with Sumproduct
 

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
--
Brevity is the soul of wit.


"Brad" wrote:

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" wrote:

What error do you get when you try to run the formula?
--
Brevity is the soul of wit.


"Brad" wrote:


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

However
=SUMPRODUCT($F$5:$F$370,--(WEEKNUM($B$5:$B$370)=$S8))/SUMPRODUCT(--($F$5:$F$3700),--(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?