Forum Discussion

Dominique Labelle's avatar
Dominique Labelle
Copper Contributor
May 19, 2017

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...
Formulas & Functions
Show and Tell
Logaraj Sekar's avatar
Logaraj Sekar
Iron Contributor
May 21, 2017

Send that excel file.

 

Or give an example.

Dominique Labelle's avatar
Dominique Labelle
Copper Contributor
May 23, 2017

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)

  • Logaraj Sekar's avatar
    Logaraj Sekar
    Iron Contributor
    May 24, 2017

    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.

    • Dominique Labelle's avatar
      Dominique Labelle
      Copper Contributor
      May 24, 2017

      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?

      • Logaraj Sekar's avatar
        Logaraj Sekar
        Iron Contributor
        May 25, 2017

        Hi Dominique Labelle,

         

        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.

         

         

         

         

Resources