Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing the last records of every month.
In a database table I have records which contain a date number and
several other value fields. There's usually one record for every day, but not necessarily. The daily value fields are cumulative monthly values for the month in question, with each month's value fields starting afresh at zero. I need a formula to sum the final records, of each month between two dates, (usually the start of the year and the current date). i.e. I want the values from the last date record in each month to be summed, ignoring all the other records preceding it in the same month. Can anyone point me in the right direction please? I'm experimenting with array formulae, which I feel will be the answer, but am struggling a bit. Only in the final analysis would I want to write some VBA code to do the job. Usual TIA, Rgds, __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing the last records of every month.
Assuming the records are sorted by date ascending (column B) starting in B2
to B366 or B367 [leap year], sum the values in E for the last record for each month. =SUMPRODUCT(E2:E367,--(MONTH(B2:B367)<MONTH(B3:B368))) -- Regards, Tom Ogilvy "Richard Buttrey" wrote in message ... In a database table I have records which contain a date number and several other value fields. There's usually one record for every day, but not necessarily. The daily value fields are cumulative monthly values for the month in question, with each month's value fields starting afresh at zero. I need a formula to sum the final records, of each month between two dates, (usually the start of the year and the current date). i.e. I want the values from the last date record in each month to be summed, ignoring all the other records preceding it in the same month. Can anyone point me in the right direction please? I'm experimenting with array formulae, which I feel will be the answer, but am struggling a bit. Only in the final analysis would I want to write some VBA code to do the job. Usual TIA, Rgds, __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing the last records of every month.
On Thu, 31 Mar 2005 11:49:59 -0500, "Tom Ogilvy"
wrote: Assuming the records are sorted by date ascending (column B) starting in B2 to B366 or B367 [leap year], sum the values in E for the last record for each month. =SUMPRODUCT(E2:E367,--(MONTH(B2:B367)<MONTH(B3:B368))) Hi Tom, Thanks for this. Like an idiot I forgot to mention one crucial (I guess) point. The records are in fact bank accounts, and there are several bank account records per day - althought not necessarily the same number per day. I'm looking to have the cumulative monthly sum total - For Each Bank Account - outside the database, rather than as an additional column in the database. Which is why I've been experimenting with array formulae which match the bank account in question, and then, which is where I'm struggling, also match the last record for each month. e.g Bank1 1/4/2005 200 Bank2 1/4/2005 100 Bank1 4/4/2005 300 Bank2 4/4/2005 50 Bank1 6/5/2005 20 Bank2 8/5/2005 40 Bank2 30/5/2005 200 Answers required for cumulative monthly totals between 1/4/2005 and 31/5/2005 (English date notation) Bank 1 320 i.e. last cum in April 300, plus last May cum 20 Bank2 250 last cum in April 50, plus last May cum 200 Kind regards, Richard __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing the last records of every month.
Really, it doesn't appear that you want the last day of the month at all.
As I see it, you actually want the last account record for each month regardless of which day it is posted on which may not be the last day of the month. I assume it isn't necessarily and probably isn't the max record in that month for that account since accounts can have a withdrawal. -- Regards, Tom Ogilvy "Richard Buttrey" wrote in message ... On Thu, 31 Mar 2005 11:49:59 -0500, "Tom Ogilvy" wrote: Assuming the records are sorted by date ascending (column B) starting in B2 to B366 or B367 [leap year], sum the values in E for the last record for each month. =SUMPRODUCT(E2:E367,--(MONTH(B2:B367)<MONTH(B3:B368))) Hi Tom, Thanks for this. Like an idiot I forgot to mention one crucial (I guess) point. The records are in fact bank accounts, and there are several bank account records per day - althought not necessarily the same number per day. I'm looking to have the cumulative monthly sum total - For Each Bank Account - outside the database, rather than as an additional column in the database. Which is why I've been experimenting with array formulae which match the bank account in question, and then, which is where I'm struggling, also match the last record for each month. e.g Bank1 1/4/2005 200 Bank2 1/4/2005 100 Bank1 4/4/2005 300 Bank2 4/4/2005 50 Bank1 6/5/2005 20 Bank2 8/5/2005 40 Bank2 30/5/2005 200 Answers required for cumulative monthly totals between 1/4/2005 and 31/5/2005 (English date notation) Bank 1 320 i.e. last cum in April 300, plus last May cum 20 Bank2 250 last cum in April 50, plus last May cum 200 Kind regards, Richard __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing the last records of every month.
On Thu, 31 Mar 2005 13:02:53 -0500, "Tom Ogilvy"
wrote: Really, it doesn't appear that you want the last day of the month at all. As I see it, you actually want the last account record for each month regardless of which day it is posted on which may not be the last day of the month. I assume it isn't necessarily and probably isn't the max record in that month for that account since accounts can have a withdrawal. Tom, You are indeed correct in that I do in fact want the last date record in each month. You're also correct in that the last date record might be smaller than a previous record in the same month because of a withdrawal, hence the max record isn't necessarily the same as the last record. Hope this helps. I'm beginning to think that I might need to code a bit of VBA to work it out, but I'd like to avoid if possible since Excel formulae are more easily understood and viewable by people who make use of the workbook. Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing the last records of every month.
Why not ask in worksheet.functions
news://msnews.microsoft.com/microsof...heet.functions -- Regards, Tom Ogilvy "Richard Buttrey" wrote in message ... On Thu, 31 Mar 2005 13:02:53 -0500, "Tom Ogilvy" wrote: Really, it doesn't appear that you want the last day of the month at all. As I see it, you actually want the last account record for each month regardless of which day it is posted on which may not be the last day of the month. I assume it isn't necessarily and probably isn't the max record in that month for that account since accounts can have a withdrawal. Tom, You are indeed correct in that I do in fact want the last date record in each month. You're also correct in that the last date record might be smaller than a previous record in the same month because of a withdrawal, hence the max record isn't necessarily the same as the last record. Hope this helps. I'm beginning to think that I might need to code a bit of VBA to work it out, but I'd like to avoid if possible since Excel formulae are more easily understood and viewable by people who make use of the workbook. Rgds __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing data by month | Excel Discussion (Misc queries) | |||
Summing data on duplicate records? | Excel Worksheet Functions | |||
Summing up Unique Records | Excel Discussion (Misc queries) | |||
Top 5 records by month | Excel Discussion (Misc queries) | |||
Summing the # of records | New Users to Excel |