Help required on Tabulating monthly expenses for the Current Month,Previous Month and Month previous to that
 

Hi

I have a data sheet where I record daily expenses date-wise. From that I tabulate the monthly expenses under "Current Month" , Month before current (Month[-1], and the Month previous to that (Month[-2]. For this, I have a column C that gives the Month serial no. as per the corresponding date, From that I calculate as follows

CURRENT MONTH EXPENSES D2= ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(NOW( )),Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0))
MONTH[-1] EXPENSES D3= ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(NOW( ))-1,Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)-D2
MONTH[-2] EXPENSES D4=ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(N OW())-2,Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)-D2-D3

This works well till December. But when it comes to January and the Month number becomes 1 again, this formula will not work for calculating previous months' cost.

I would be immensely grateful if I could get an idea on how to trap the previous months' expenses when the current month becomes January

Thanks

Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
 

Hi,

Am Sat, 9 Aug 2014 10:49:03 -0700 (PDT) schrieb San:

CURRENT MONTH EXPENSES D2= ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(NOW( )),Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0))
MONTH[-1] EXPENSES D3= ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(NOW( ))-1,Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)-D2
MONTH[-2] EXPENSES D4=ROUND(SUM(INDEX(Cost!$A$2:$Y$3615,MATCH(MONTH(N OW())-2,Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)-D2-D3

try it with a real date:

=MONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),1))
=MONTH(DATE(YEAR(TODAY()),MONTH(TODAY()),0))
=MONTH(DATE(YEAR(TODAY()),MONTH(TODAY())-1,0))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
 

Hi again,

Am Sat, 9 Aug 2014 10:49:03 -0700 (PDT) schrieb San:

I have a data sheet where I record daily expenses date-wise. From that I tabulate the monthly expenses under "Current Month" , Month before current (Month[-1], and the Month previous to that (Month[-2]. For this, I have a column C that gives the Month serial no. as per the corresponding date, From that I calculate as follows

if your date is in column B and the values in D:Y you can also use:

=SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY()))*Cost!D1:Y1000)
=SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY())-1)*Cost!D1:Y1000)
=SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY())-2)*Cost!D1:Y1000)



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
 

Thanks Claus. Will try the same and revert back

San


On Saturday, August 9, 2014 11:55:56 PM UTC+5:30, Claus Busch wrote:
Hi again,



Am Sat, 9 Aug 2014 10:49:03 -0700 (PDT) schrieb San:



I have a data sheet where I record daily expenses date-wise. From that I tabulate the monthly expenses under "Current Month" , Month before current (Month[-1], and the Month previous to that (Month[-2]. For this, I have a column C that gives the Month serial no. as per the corresponding date, From that I calculate as follows



if your date is in column B and the values in D:Y you can also use:



=SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY()))*Cost!D1:Y1000)

=SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY())-1)*Cost!D1:Y1000)

=SUMPRODUCT((YEAR(Cost!B1:B1000)=YEAR(TODAY()))*(M ONTH(Cost!B1:B1000)=MONTH(TODAY())-2)*Cost!D1:Y1000)







Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
 

Here's a sample of how I do this...

https://app.box.com/s/23yqum8auvzx17h04u4f

Look for the file "IncomeExpense.xlt"!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
 

Hi,

Am Sat, 9 Aug 2014 11:36:03 -0700 (PDT) schrieb San:

Will try the same and revert back

you can also use:

=MONTH(TODAY())
=MONTH(EDATE(TODAY(),-1))
=MONTH(EDATE(TODAY(),-2))


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
 

On Sunday, August 10, 2014 8:29:34 PM UTC+5:30, Claus Busch wrote:
Hi,



Am Sat, 9 Aug 2014 11:36:03 -0700 (PDT) schrieb San:



Will try the same and revert back



you can also use:



=MONTH(TODAY())

=MONTH(EDATE(TODAY(),-1))

=MONTH(EDATE(TODAY(),-2))





Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thanks again Claus. This is far simpler than the first one and it is working. BTW if I need to trap the expenses week-wise say for the current week, Week[-1] and Week[-2] I can use the WEEKNUM(Today(),2), WEEKNUM(Today(),2)-1 and WEEKNUM(Today(),2)-2 for getting the same. But again I face the same problem when the Current week is the 1st week of January.

Any help on that ?

Thanks

Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
 

Hi,

Am Sun, 10 Aug 2014 18:45:10 -0700 (PDT) schrieb San:

BTW if I need to trap the expenses week-wise say for the current week, Week[-1] and Week[-2] I can use the WEEKNUM(Today(),2), WEEKNUM(Today(),2)-1 and WEEKNUM(Today(),2)-2 for getting the same. But again I face the same problem when the Current week is the 1st week of January.

try:
=WEEKNUM(TODAY(),2)
=WEEKNUM(TODAY()-7,2)
=WEEKNUM(TODAY()-14,2)



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
 

On Monday, August 11, 2014 11:49:10 AM UTC+5:30, Claus Busch wrote:
Hi,



Am Sun, 10 Aug 2014 18:45:10 -0700 (PDT) schrieb San:



BTW if I need to trap the expenses week-wise say for the current week, Week[-1] and Week[-2] I can use the WEEKNUM(Today(),2), WEEKNUM(Today(),2)-1 and WEEKNUM(Today(),2)-2 for getting the same. But again I face the same problem when the Current week is the 1st week of January.



try:

=WEEKNUM(TODAY(),2)

=WEEKNUM(TODAY()-7,2)

=WEEKNUM(TODAY()-14,2)







Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Great!.. I should have thought in that line. Thanks

Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
 

On Monday, August 11, 2014 12:08:24 PM UTC+5:30, San wrote:
On Monday, August 11, 2014 11:49:10 AM UTC+5:30, Claus Busch wrote:

Hi,







Am Sun, 10 Aug 2014 18:45:10 -0700 (PDT) schrieb San:







BTW if I need to trap the expenses week-wise say for the current week, Week[-1] and Week[-2] I can use the WEEKNUM(Today(),2), WEEKNUM(Today(),2)-1 and WEEKNUM(Today(),2)-2 for getting the same. But again I face the same problem when the Current week is the 1st week of January.







try:



=WEEKNUM(TODAY(),2)



=WEEKNUM(TODAY()-7,2)



=WEEKNUM(TODAY()-14,2)















Regards



Claus B.



--



Vista Ultimate / Windows7



Office 2007 Ultimate / 2010 Professional



Great!.. I should have thought in that line. Thanks

If I have to sum on a Financial Year-wise basis (i.e. from April - Mar of next year), I have inserted a column where the Financial Year is calculated as follows:

E2=IF(OR(C2=1,C2=2,C2=3),YEAR(A2)-1,YEAR(A2)) [where C2 = Month serial no.of A2)


Now for Financial Year-wise tabulation, say for the last Financial Year, the formula in Cell T31 is

T31= IF(OR(MONTH(TODAY())=1,MONTH(TODAY())=2,MONTH(TODA Y())=3),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615,MATCH( YEAR(EDATE(TODAY(),-12))-1,EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0)-SUM(T$30:T30),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615, MATCH(YEAR(EDATE(TODAY(),-12)),EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0))-SUM(T$30:T30)... where Cell T30 gives the summated value for the current Financial Year

Any simpler formula for the above?

Thanks

San

Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
 

Hi,

Am Tue, 26 Aug 2014 05:03:04 -0700 (PDT) schrieb San:

T31= IF(OR(MONTH(TODAY())=1,MONTH(TODAY())=2,MONTH(TODA Y())=3),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615,MATCH( YEAR(EDATE(TODAY(),-12))-1,EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0)-SUM(T$30:T30),ROUND(SUM(INDEX(EMV5!$AA$2:$AA$3615, MATCH(YEAR(EDATE(TODAY(),-12)),EMV5!$E$2:$E$3615,0)):EMV5!$AA$3615),0))-SUM(T$30:T30)... where Cell T30 gives the summated value for the current Financial Year

if you have to sum EMV!AA2:AA3615 try:
=SUMPRODUCT(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())-1),--(MONTH(EMV!C2:C3615)=4),EMV!AA2:AA3615)+SUMPRODUC T(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())),--(MONTH(EMV!C2:C3615)<=3),EMV!AA2:AA3615)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help required on Tabulating monthly expenses for the Current Month, Previous Month and Month previous to that
 

Hi again,

Am Tue, 26 Aug 2014 14:53:05 +0200 schrieb Claus Busch:

if you have to sum EMV!AA2:AA3615 try:
=SUMPRODUCT(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())-1),--(MONTH(EMV!C2:C3615)=4),EMV!AA2:AA3615)+SUMPRODUC T(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())),--(MONTH(EMV!C2:C3615)<=3),EMV!AA2:AA3615)

sorry, there is an error. Column C are numbers of month so you do not
need MONTH
Here is a shorter suggestion:
=SUMPRODUCT((((YEAR(EMV!A2:A3615)=YEAR(TODAY())-1)*(EMV!C2:C3615=4))+((YEAR(EMV!A2:A3615)=YEAR(TO DAY()))*(EMV!C2:C3615<=3)))*EMV!AA2:AA3615)



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Help required on Tabulating monthly expenses for the CurrentMonth, Previous Month and Month previous to that
 

On Tuesday, August 26, 2014 7:00:58 PM UTC+5:30, Claus Busch wrote:
Hi again,



Am Tue, 26 Aug 2014 14:53:05 +0200 schrieb Claus Busch:



if you have to sum EMV!AA2:AA3615 try:

=SUMPRODUCT(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())-1),--(MONTH(EMV!C2:C3615)=4),EMV!AA2:AA3615)+SUMPRODUC T(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())),--(MONTH(EMV!C2:C3615)<=3),EMV!AA2:AA3615)



sorry, there is an error. Column C are numbers of month so you do not

need MONTH

Here is a shorter suggestion:

=SUMPRODUCT((((YEAR(EMV!A2:A3615)=YEAR(TODAY())-1)*(EMV!C2:C3615=4))+((YEAR(EMV!A2:A3615)=YEAR(TO DAY()))*(EMV!C2:C3615<=3)))*EMV!AA2:AA3615)







Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional

Thanks Claus for this simple formula. I have only removed the "month" tag in the formula as C column is giving the serial no. of the month, which I presume is not a date field any more. After that it worked fine!

San

=SUMPRODUCT(--(YEAR(EMV!D2:D3615)=YEAR(TODAY())-1),--(EMV!C2:C3615=4),EMV!AA2:AA3615)+SUMPRODUCT(--(YEAR(EMV!D2:D3615)=YEAR(TODAY())),--(EMV!C2:C3615<=3),EMV!AA2:AA3615)