Results 1 to 8 of 8

sum a range using match and index

This is a discussion on sum a range using match and index within the Excel Questions forums, part of the Question Forums category; Hi hope someone can point me in the right direction here. I have gleaned over the posts and may have ...

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    Bedfordshire (UK)
    Posts
    970

    Default sum a range using match and index

    Hi
    hope someone can point me in the right direction here. I have gleaned over the posts and may have missed the answer so here goes...

    I have a number of worksheets with different bits of data on them but all have a common reference number and always in column A

    I am trying to sum a row (say from BN:DF) based on a match and index formula.
    these are my attempts.
    • =SUM(INDEX(Sales!BN:DF,MATCH(Sheet1!A2,Sales!A:A,0)))
    • =SUM(Sales!(match(A2,Sales!A:A,0)BNmatch(A2,Sales!A:A,0))))
    It would appear that both are total drivel.

  2. #2
    MrExcel MVP
    Moderator

    In The Naughty Corner
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    10,087

    Default Re: sum a range using match and index

    Welcome to the board

    Are you trying to sum up the common references across all sheets? Or one sheet at a time?

    If the first - what are the sheet names?

  3. #3
    Board Regular cornflakegirl's Avatar
    Join Date
    Nov 2004
    Location
    UK
    Posts
    2,021

    Default Re: sum a range using match and index

    If you're trying to sum one row between BN and DF, then your first formula is nearly right - you just need a zero as the column argument for index:

    =SUM(INDEX(Sales!BN:DF,MATCH(Sheet1!A2,Sales!A:A,0),0))
    Emma

    Your apparent nonchalance belies the fact that you can only think of me.

  4. #4
    Board Regular
    Join Date
    Oct 2008
    Location
    Bedfordshire (UK)
    Posts
    970

    Default Re: sum a range using match and index

    Whoa! I dont believe it - spot on thanks cornflakegirl

  5. #5
    New Member
    Join Date
    Oct 2014
    Posts
    2

    Default Re: sum a range using match and index

    How can I do the same for column

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    27,049

    Default Re: sum a range using match and index

    Quote Originally Posted by spk26255 View Post
    How can I do the same for column
    Welcome to the MrExcel board!

    Something like this?

    Sum Column

     BCDEFGHI
    2   Header 1Header 2Header 3Header 4Header 5
    3Header to FindCol Sum 26576976
    4Header 4243 3549875
    5   4 7986
    6   5587006
    7   469976
    8   37 96
    9   47006
    10   5425887
    11   6586796
    12   768986
    13   77696786
    14        

    Spreadsheet Formulas
    CellFormula
    C4=SUM(INDEX(E3:I13,0,MATCH(B4,E2:I2,0)))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  7. #7
    New Member
    Join Date
    Oct 2014
    Posts
    2

    Default Re: sum a range using match and index

    Thanks a ton. It solved my Purpose

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    27,049

    Default Re: sum a range using match and index

    Quote Originally Posted by spk26255 View Post
    Thanks a ton. It solved my Purpose
    Cheers. Thanks for letting us know.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com