Summing the last records of every month.

[Richard Buttrey]

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
__________________________

 

[Tom Ogilvy]

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

 

[Richard Buttrey]

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
__________________________

 

[Tom Ogilvy]

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

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
__________________________

 

[Richard Buttrey]

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
__________________________

 

[Tom Ogilvy]

Why not ask in worksheet.functions

news://msnews.microsoft.com/microsoft.public.excel.worksheet.functions