• 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.

Last 12 Months of data based on date

R

Razar

New Member
I am looking for a formula that will sum the last 12 months of data based on a date, including the month of that date. For example, based on the end date of 4/30/13, I would like to sum data from May, 2012 to April, 2013. I also do not want to have to update the formula each month, but rather update the date in a cell that the formula references. Please help!
 
Razar

=Sumproduct((DateRange>Eomonth(date_cell,-12)*(DateRange<=Eomonth(date_cell,0)*(Data range))
 
I need to also reference store numbers - So my on my summary tab, I have store numbers in cells B5:B11. I also have the the date I want my formula to reference in cell B2. The sales I want to sum are in a second tab with each store on its own line, the numbers in column A) and monthly sales going across, from columns B:AM. The header row of months is row 6.
 
Can you post a sample file?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi Razar,


Try this 1% modified formula of Hui :)


Code: 
=SUMPRODUCT((C6:W6>EDATE(SUMMARY!A2,-12))*(C6:W6<=EDATE(SUMMARY!A2,0))*($B$7:$B$27=B12)*($C$7:$W$27))


Every thing with reference to your sample file.


Regards,
 
Hi Razar,


Please elaborate briefly or always upload file with expected output and area.. where formula need to paste..


Still with my low level guessing technique.. I hope..

* You want to lookup sum area from DATA sheet according to provided data and store number..


So in J5, (I guess your expected output area!!!)

use formula as..

Code: 
=SUM(OFFSET(DATA!$B$6,MATCH($B5,DATA!$B$7:$B$27,0),MATCH(EOMONTH($A$2,-12)+1,DATA!$C$6:$W$6,0),1,12))


https://dl.dropboxusercontent.com/u/78831150/Excel/Last%2012%20Months%20of%20data%20based%20on%20date%20%28razar%29.xlsx


Regards,

Deb
 
Hi, Razar!


Try doing this:


a) Define 2 named ranges called StoreList and MonthList as:

StoreList: =DESREF(DATA!$B$7;;;CONTARA(DATA!$B:$B)-1;1) -----> in english: =OFFSET(DATA!$B$7,,,COUNTA(DATA!$B:$B)-1,1)

MonthList: =DESREF(DATA!$C$6;;;1;CONTARA(DATA!$6:$6)-1) -----> in english: =OFFSET(DATA!$C$6,,,1,COUNTA(DATA!$6:$6)-1)


b) Place this formula in column J and copy down as required:

J5: =SUMA(INDIRECTO(DIRECCION(COINCIDIR(B5;StoreList;0)+FILA(StoreList)-1;COINCIDIR(FECHA(AÑO($A$2);MES($A$2)-11;1);MonthList;0)+COLUMNA(MonthList)-1;4;1;"DATA")&":"&DIRECCION(COINCIDIR(B5;StoreList;0)+FILA(StoreList)-1;COINCIDIR($A$2-DIA($A$2)+1;MonthList;0)+COLUMNA(MonthList)-1;4;1))) -----> in english: =SUM(INDIRECT(ADDRESS(MATCH(B5,StoreList,0)+ROW(StoreList)-1,MATCH(DATE(YEAR($A$2),MONTH($A$2)-11,1),MonthList,0)+COLUMN(MonthList)-1,4,1,"DATA")&":"&ADDRESS(MATCH(B5,StoreList,0)+ROW(StoreList)-1,MATCH($A$2-DAY($A$2)+1,MonthList,0)+COLUMN(MonthList)-1,4,1)))


Just advise to Debray Roy if any issue.


Regards!
 
You must log in or register to reply here.
Back
Top