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

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

C

#### Claus Busch

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

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.

C

#### Claus Busch

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()))*(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.

S

#### San

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

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

C

#### Claus Busch

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.

S

#### San

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

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

C

#### Claus Busch

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.

S

#### San

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

S

#### San

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.
=WEEKNUM(TODAY(),2)
=WEEKNUM(TODAY()-7,2)
=WEEKNUM(TODAY()-14,2)

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

C

#### Claus Busch

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

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.

C

#### Claus Busch

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)

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.

S

#### San

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

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 3615)=YEAR(TODAY())-1),--(EMV!C2:C3615>=4),EMV!AA2:AA3615)+SUMPRODUCT(--(YEAR(EMV!D2 3615)=YEAR(TODAY())),--(EMV!C2:C3615<=3),EMV!AA2:AA3615)