On Sun, 17 May 2009 16:03:06 -0400, Ron Rosenfeld
wrote:

On Sun, 17 May 2009 18:51:43 GMT, pub wrote:




Given that, this **array-entered** formula should do what you want.

=SUM(TRANSPOSE(WEEKDAY(ROW(INDIRECT(A1-DAY(A1)+1&":"&
A1-DAY(A1)+32-DAY(A1-DAY(A1)+32))))<6)*(D1:AH112)*(D1:AH1-12))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.


I just noticed something and we need to change this formula. Otherwise we will
wind up with different sized arrays in the month vs the columns, which will
return an error.

For simplicity, I assumed that A1 would always contain the FIRST day of the
month in question. Given that, this **array** formula should do the trick:

=SUM(TRANSPOSE(WEEKDAY(ROW(INDIRECT(A1&":"&A1+32-DAY(A1+32))))<6)*
(OFFSET(D1,0,0,1,DAY(A1+32-DAY(A1+32)))12)*(OFFSET(D1,0,0,1,DAY(A1+32-DAY(A1+32)))-12))

--ron