Sumproduct W-t-d
 

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


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=564100

Sumproduct W-t-d
 

try this

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


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=564100

Sumproduct W-t-d
 

I was unable to get the following to work.

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

However, after a few modifications the following returns the expected
answer.

=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)

Many thanks.

-ep


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=564100

Sumproduct W-t-d
 

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.

Any ideas?

-ep


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile: http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=564100

Sumproduct W-t-d
 

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

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"edwardpestian"
wrote in message
news:edwardpestian.2bepbs_1153671908.5181@excelfor um-nospam.com...

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.

Any ideas?

-ep


--
edwardpestian
------------------------------------------------------------------------
edwardpestian's Profile:
http://www.excelforum.com/member.php...o&userid=33809
View this thread: http://www.excelforum.com/showthread...hreadid=564100