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

  • Thread starter Thread starter San
  • Start date Start date
S

San

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(NOW())-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
 
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(NOW())-2,Cost!$C$2:$C$3615,0),25):Cost!$Y$3615),0)-D2-D3
Click to expand...

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.
 
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
Click to expand...

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

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



Regards
Claus B.
 
Thanks Claus. Will try the same and revert back

San


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 acolumn C that gives the Month serial no. as per the corresponding date, From that I calculate as follows
Click to expand...



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



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

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

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







Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...
 
Hi,

Am Sat, 9 Aug 2014 11:36:03 -0700 (PDT) schrieb San:
Will try the same and revert back
Click to expand...

you can also use:

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


Regards
Claus B.
 
Hi,



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






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
Click to expand...

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)-1and 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
 
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.
Click to expand...

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



Regards
Claus B.
 
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.
Click to expand...



try:

=WEEKNUM(TODAY(),2)

=WEEKNUM(TODAY()-7,2)

=WEEKNUM(TODAY()-14,2)







Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

Great!.. I should have thought in that line. Thanks
 
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.
Click to expand...
try:
Click to expand...
=WEEKNUM(TODAY(),2)
Click to expand...
=WEEKNUM(TODAY()-7,2)
Click to expand...
=WEEKNUM(TODAY()-14,2)
Click to expand...
Regards
Click to expand...

Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Click to expand...



Great!.. I should have thought in that line. Thanks
Click to expand...

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(TODAY())=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
 
Hi,

Am Tue, 26 Aug 2014 05:03:04 -0700 (PDT) schrieb San:
T31= IF(OR(MONTH(TODAY())=1,MONTH(TODAY())=2,MONTH(TODAY())=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
Click to expand...

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)+SUMPRODUCT(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())),--(MONTH(EMV!C2:C3615)<=3),EMV!AA2:AA3615)


Regards
Claus B.
 
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)+SUMPRODUCT(--(YEAR(EMV!A2:A3615)=YEAR(TODAY())),--(MONTH(EMV!C2:C3615)<=3),EMV!AA2:AA3615)
Click to expand...

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(TODAY()))*(EMV!C2:C3615<=3)))*EMV!AA2:AA3615)



Regards
Claus B.
 
Hi again,



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







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(TODAY()))*(EMV!C2:C3615<=3)))*EMV!AA2:AA3615)







Regards

Claus B.

--

Vista Ultimate / Windows7

Office 2007 Ultimate / 2010 Professional
Click to expand...

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