Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct causing memory errors? | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |