• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code to know the sum from current month to previous 11 months

Shailender

Shailender

Member
Hello All, I am trying to write a VBA code based on the values given in the excel sheet. As you can see in the below a column called Current 12 months,it will sum up from current month to previous 11 months, like wise prior 12 months should also happen. Every month new column is getting inserted like 5/31/2016,6/30/2016,7/31/2016 and so on. It should calculate from present month to previous 11 months.

Please do the needful.

Any other thoughts that would be greatly appreciated.

Thank you
 

Attachments

  • Question.xlsx
    9.5 KB · Views: 7
Hello Hui,

Thank you for the reply. Could you please explain the above formula in detail like why did you used A2, countTA and 2:2,-13,1, and -12.

Please do the needful.

Thank you!
 
Hello Hui,

I forgot to mention that the formula is working pretty well. Just need a explanation.

Thank you!
 
Hello Hui,

Thank you for the explanation. In my case the formula is not working.

I am attaching the excel sheet please refer to sheet2.

I need the formula to be used in GE and GF. And i also i highlighted the column FS and FT there every month new to be added before the minimum column.

Thank you in advance
 

Attachments

  • Question.xlsx
    20.5 KB · Views: 6
They don't work on sheet 2 as the data layout is totally different top sheet1
The Summary data is in the same row as the data and so it stuffs up the whole formula

If you are going to insert a row each month why not just use
GE2: =SUM(EW2:FH2)
GF2: =SUM(FI2:FT2)

Otherwise please read http://chandoo.org/wp/2012/09/17/offset-formula-explained/

I generally don't recommend having summary data at the end of rows for this reason

I cannot answer this for the next week as I am traveling o/s for work
 
Hi,

Narayank thank you for your reply.

But it is not exactly which i am looking for. As i see in the sheet you have taken everyday date wherein i need everymonth end date. If u observe in the sheet there is a name called Min beside that one new column is going to included. From that current month date to previous 11 months data i need to sumup. Same as for prior 12 months. it should be done dynamically.

Here is the attached sheet for your reference. I included the comment box as well.

Thank you in advance.
 

Attachments

  • Question.xlsx
    25.2 KB · Views: 6
Hi ,

I can understand that the Current 12 months refers to the period :

September 2015 through August 2016 ( since this is the last month for which data is available ).

I am not able to understand how the Prior 12 months refers to the period :

September 2013 through August 2014

Can you clarify ?

Narayan
 
Hi,
As you can see there are two tabs one is current 12 months and other one is prior 12 months.
Here I need to use the formula what I used in current 12 months. Since I have the data from 2002. In prior 12 months I need to use the criteria
Which I used in current 12 months.

Hope it is clear now.

Thank you.
 
Hi ,

No , it is still not clear.

In one of your posts , you have mentioned that you want formulae for the cells GE2 and GF2 ; I see that in these two cells , you have put in the formulae :

GE2 - =SUM(EK2:EV2)

GF2 - =SUM(FI2:FT2)

Are you referring to these two cells even now , or is the formula you want for cells A6 and B6 ?

Narayan
 
Hi,

Sorry for the confusion. I am referring to the sheet2 GE2 and GF2 not in the sheet1. As an example i asked in the sheet1 but still it was not working. so I made it clear in the sheet2.

Thank you.
 
Hi ,

If you can clarify ?

I had posted this earlier :
I can understand that the Current 12 months refers to the period :

September 2015 through August 2016 ( since this is the last month for which data is available ).

I am not able to understand how the Prior 12 months refers to the period :

September 2013 through August 2014

Can you clarify ?
Click to expand...
You have put in the following formulae in GE2 and GF2 :
GE2 - =SUM(EK2:EV2)

GF2 - =SUM(FI2:FT2)
Click to expand...
The range EK2:EV2 is referring to the period September 2013 through August 2014

Can you explain why this should not be September 2014 through August 2015 ?

Narayan
 
Hi Narayan,

I am extremely sorry for the confusion. its my mistake.

Lets say for Current 12 months it is calculated from present month as i mentioned in the sheet 8/31/2016 to 11 months so it will be till 9/30/2015. Likewise for Previous 12 months From 8/31/2015 to 11 months so it will be till 9/30/2014. so it should be done dynamically every month. For your reference i am attaching the excel sheet.

Thank you!
Shailender
 

Attachments

  • Question.xlsx
    25.2 KB · Views: 2
My thought is a VBA code is very not necessary ! Just with Excel basics …
And above all, the way of Hui & NARAYANK991 is the easy way to go ‼
After updating NARAYANK991's formula
- as it is very not difficult to count the columns offset yourself ‼ -
I got expected values …

Another way based on post #10 attachment :
'cause my test computer is under an old local version (not English native),
my formulas are longer than those you should make yourself ‼
(You can shorter formulas just by reading inner Excel help date functions …)

GE2 cell formula :​
Code: 
=SUMPRODUCT(($A$1:FT$1>=DATE(YEAR(FT$1)-3,MONTH(FT$1)+2,1)-1)*($A$1:FT$1<=DATE(YEAR(FT$1)-2,MONTH(FT$1),DAY(FT$1)))*$A2:FT2)

GF2 cell formula :​
Code: 
=SUMPRODUCT(($A$1:FT$1>=DATE(YEAR(FT$1)-1,MONTH(FT$1)+2,1)-1)*$A2:FT2)
Do you like it ? So thanks to click on bottom right Like !
​
 
You must log in or register to reply here.
Back
Top