Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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
    1,054

    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

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    10,492

    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?
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs

    Blog: Excel Evolution Blog | Twitter: @ExcelEvo | Facebook: Follow me here

    English is a weird language. It can be understood through tough thorough thought, though!

  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
    1,054

    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
    33,067

    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
    Excel 2010 (mainly), 2016, 2013, 2007, 2003 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  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
    33,067

    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
    Excel 2010 (mainly), 2016, 2013, 2007, 2003 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  9. #9
    New Member
    Join Date
    Feb 2016
    Posts
    1

    Default Re: sum a range using match and index

    Hi,

    Can any one please help me out...I want to add Billing for Sales person mention in Coloumn K ...I have tried combination of Sumif with Index & match..but may be I am missing something it shows as #NA#....

    Following is the Data:

    Billing Direct cost Margin % Sales person
    27,600 14000 13,600 49% Aniket
    312,900 254875 58,025 19% Caleb
    254,100 227382 26,718 11% Caleb
    75,000 0 75,000 100% Pradeep
    50,000 0 50,000 100% Pradeep
    273,000 0 273,000 100% Caleb
    30,000 21000 9,000 30% I****a
    30,000 21000 9,000 30% I****a
    535,501 394775 140,726 26% Manish
    250,005 158125 91,880 37% Caleb
    112,500 7000 105,500 94% Manish
    108,975 71845 37,130 34% Manish
    6,000 4000 2,000 33% Manish
    188,351 126765 61,586 33% I****a
    53,815 54131 -316 -1% I****a
    283,422 189075 94,347 33% I****a

  10. #10
    Board Regular skywriter's Avatar
    Join Date
    Feb 2014
    Posts
    1,631

    Default Re: sum a range using match and index

    Tigress,

    Welcome to the board.

    Please start your own thread. Posting a question in a thread not started by you is considered hijacking. If you feel this thread is relevant to your issue, then post a link to it in your new thread.

    Thanks.
    Last edited by skywriter; Feb 17th, 2016 at 12:45 AM.
    Bruce
    ______________________________________________________
    There is no better way to learn than by doing.

    - Windows 7 -- Excel, Access & Word 2010
    - Post your spreadsheet online: Dropbox
    - Posting guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

Page 1 of 2 12 LastLast

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