Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    48150
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Index Formula Help

    I am trying to add a total number of sales from January to a specific month. This specific month would be referenced in a cell say B4. If B4 changes (July to August) the formula would now total Jan through Aug. If B4 changed to DEC then the formula would change to sum Jan to Dec.

    I am new to the index function and feel this would be my answer but I am stuck.

    I have attached a sample file that gives the general idea.

    Any help is greatly appreciated

    JG
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    =SUM(OFFSET($A$2,0,0,1,MATCH($B$4,$A$1:$L$1,0)))

    Match() returns the column no in which the month in cell B4 is found. Offset() returns a 1 by N reference starting at A2 and ending at the desired month. Sum() adds up the values.

    You might want to extend that to check Match() doesn't return an error, in case someone enters an invalid date, or you could use data validation to make B4 a drop down list of the months in A1-A12.

    OK?

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Since I prefer INDEX as it is only semi-volatile, I would use:
    =SUM(A2:INDEX(A2:L2,MATCH(B4,$A$1:$L$1,0)))
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •