Results 1 to 4 of 4

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
    963

    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

    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    9,897

    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 and terms of use

    - Post your sheet using HTML tables - why?

    - Read the FAQs

    - Always use CODE tags when posting code, e.g. [CODE]insert code here[/CODE]

    - My Excel Blog

  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
    963

    Default Re: sum a range using match and index

    Whoa! I dont believe it - spot on thanks cornflakegirl

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