Forum Discussion
Last 6 months
Hey, I am tracking the number of falls for about 100 people, but need to know how many falls each have had in the last 6 months. I would like for this number to automatically update with the curr...
Send that excel file.
Or give an example.
I cant send the file due to confidentiality issues, but this is essentially what it looks like:
I would like for column P to have the total for the last 6 months, that would change with the current date (ex - today is May 23 so i would want the total for November 23-Dec 23, when I open the file in June, I'd like the total to be for Dec-May)
Okay.
But, see.
As per screen shot, You have provided a sheet having particulars for 'Jan - Dec'. It look like 'Jan 2017 - Dec 2017', same year.
If you want to add last 6 months total, you need 'Nov 2016, Dec 2016, Jan 2017, Feb 2017, Mar 2017, Apr 2017'.
But for particulars for 4 months only available. Where is Nov 2016, Dec 2016?
Because you have specified that you need 'last 6 months total'.
That is the only thing confusing me.
Clarify this.
it shouldnt matter if my screenshot doesnt have the data for the last 6 months - pretend it is october today. or even if it was for just the last 4 months - I would just need to change a portion of the formula?
I am beginner of excel. So gave formula upto my knowledge. I also attached a file here with.
That file will work based on opening month only.
Ask me if any clarifications and my formula is
=IF(AND(MONTH($C$1)<MONTH(TODAY()),MONTH($C$1)>MONTH(TODAY())-7),HLOOKUP($C$1,$C$1:$C$11,ROW(),0))+IF(AND(MONTH($D$1)<MONTH(TODAY()),MONTH($D$1)>MONTH(TODAY())-7),HLOOKUP($D$1,$D$1:$D$11,ROW(),0))+IF(AND(MONTH($E$1)<MONTH(TODAY()),MONTH($E$1)>MONTH(TODAY())-7),HLOOKUP($E$1,$E$1:$E$11,ROW(),0))+IF(AND(MONTH($F$1)<MONTH(TODAY()),MONTH($F$1)>MONTH(TODAY())-7),HLOOKUP($F$1,$F$1:$F$11,ROW(),0))+IF(AND(MONTH($G$1)<MONTH(TODAY()),MONTH($G$1)>MONTH(TODAY())-7),HLOOKUP($G$1,$G$1:$G$11,ROW(),0))+IF(AND(MONTH($H$1)<MONTH(TODAY()),MONTH($H$1)>MONTH(TODAY())-7),HLOOKUP($H$1,$H$1:$H$11,ROW(),0))+IF(AND(MONTH($I$1)<MONTH(TODAY()),MONTH($I$1)>MONTH(TODAY())-7),HLOOKUP($I$1,$I$1:$I$11,ROW(),0))+IF(AND(MONTH($J$1)<MONTH(TODAY()),MONTH($J$1)>MONTH(TODAY())-7),HLOOKUP($J$1,$J$1:$J$11,ROW(),0))+IF(AND(MONTH($K$1)<MONTH(TODAY()),MONTH($K$1)>MONTH(TODAY())-7),HLOOKUP($K$1,$K$1:$K$11,ROW(),0))+IF(AND(MONTH($L$1)<MONTH(TODAY()),MONTH($L$1)>MONTH(TODAY())-7),HLOOKUP($L$1,$L$1:$L$11,ROW(),0))+IF(AND(MONTH($M$1)<MONTH(TODAY()),MONTH($M$1)>MONTH(TODAY())-7),HLOOKUP($M$1,$M$1:$M$11,ROW(),0))+IF(AND(MONTH($N$1)<MONTH(TODAY()),MONTH($N$1)>MONTH(TODAY())-7),HLOOKUP($N$1,$N$1:$N$11,ROW(),0))
I told i was a beginner.
