SUMPRODUCT W-T-D

[inked]

I have the following formula that calculates a MTD total based on the date in AL3. Cells B5:AF5 contain the date range 07/01/2006 through 07/31/2006, and cells B6:AF6 contain the numbers to be summed.

=SUMPRODUCT(--($B$5:$AF$5<=$AL$3),$B6:$AF6)

I need to take this a step further and calculate a Week to Date value based on the date in AL3. The week is Monday - Sunday. So for example, if the date in AL3 is 07/21/2006 then the formula would sum the previous monday through 07/21/2006: In other words the formula would sum the values from 07/17/2006 (Monday) through 07/21/2006.

If the date is changed to, for example, 07/09 the formula would sum the values from 07/03 (Monday) through 07/09

Thanks in advance

-ep

[Bob Phillips]

[vba]

=SUMPRODUCT(--($B$5:$AF$5>=$AL$3-WEEKDAY($AL$3,2)+1),--($B$5:$AF$5<$AL$3-WEEKDAY($AL$3,2)+8),$B6:$AF6)
[/vba]

[inked]

Voila!

Thank you kindly...

-ep

[inked]

Oops...not exactly working. When I select a date within the middle of the week, Wednesday 07/12 for example, the formula returns the W-T-D for that entire week. I need it to return the W-T-D only for the previous Monday (07/09) through 07/12 as opposed to 07/16.

Thanks again.

-ep

[Bob Phillips]

Sorry, you did say that6, I just got it wrong

=SUMPRODUCT(--($B$5:$AF$5>=$AL$3-WEEKDAY($AL$3,2)+1),--($B$5:$AF$5<=$AL$3),$B6:$AF6)

[inked]

The revision did, in fact, work as needed.

Many Thanks.

-ep