Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total back to w/c Monday
Hi,
I have a table of data that counts various items on a daily basis. The data is arranged in a table - Date - Item 1 - Item 2 - Item 3 etc I need to count the total as each week progresses from that weeks Monday. Can anyone advise on a formula to count each column total from the last Monday (date) in the table?. The forumla would ned to find the Monday of the current week and total the sum of each day of the current week. Cheers, Mickey |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total back to w/c Monday
To sum column B where date in column A is previous Monday or greater... =SUMIF(A:A,"="&TODAY()-WEEKDAY(TODAY(),3),B:B) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=568116 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total back to w/c Monday
Hi & Thanks for the formula,
Two small points if possible for clarification please. The formula is counting one day too many and I'm not sure why that is and will take a look at that in the morning as it is late just now. Please could you tell me what the number 3 element of the formula refers to?. Thanks, Mickey "daddylonglegs" wrote in message news:daddylonglegs.2bzjbu_1154643910.0679@excelfor um-nospam.com... To sum column B where date in column A is previous Monday or greater... =SUMIF(A:A,"="&TODAY()-WEEKDAY(TODAY(),3),B:B) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=568116 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Total back to w/c Monday
Formula should include Monday and all subsequent dates, is that not correct? =TODAY()-WEEKDAY(TODAY(),3) gives the date of the previous Monday (on Monday it gives that day's date) WEEKDAY function has 3 possible settings, I'm using the third one (hence the 3) which assigns numbers to days as follows Monday = 0 Tuesday = 1 ....etc. Sunday = 6 -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=568116 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Sub total and totals | Excel Worksheet Functions | |||
Counting number of forwarding and sending back | Excel Discussion (Misc queries) | |||
Desperately need help with 3 calculations | Excel Worksheet Functions | |||
PivotTable-% of total | Excel Worksheet Functions | |||
Pivot table for reporting sales performance | Excel Discussion (Misc queries) |