+ Reply to Thread
Results 1 to 5 of 5

Sum Index Match help needed

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    25

    Sum Index Match help needed

    Greetings all, I would like to know if it is possible to sum consecutive cells (in a row for instance), based on criteria matching the Column headers. The attached excel will provide further clarity to this question.

    Thanks you very much!
    Attached Files Attached Files

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Sum Index Match help needed

    Please Login or Register  to view this content.
    This will sum all the dollar values in B2:H2 according to two criteria:
    1) The corresponding number above it in range B1:H1 is more than or equal to the value in D9
    2) The corresponding number above it in range B1:H1 is less than D9+E9

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    25

    Re: Sum Index Match help needed

    Quote Originally Posted by ben_hensel View Post
    Please Login or Register  to view this content.
    This will sum all the dollar values in B2:H2 according to two criteria:
    1) The corresponding number above it in range B1:H1 is more than or equal to the value in D9
    2) The corresponding number above it in range B1:H1 is less than D9+E9
    Thank yo Ben_hensel

    Good start but the formula needs to consider the value in F9 and match it to the appropriate row in my table (say A1:H3). If the value in F9 was HE00140, I need the formula to use the row corresponding to the value from F9 - in this second instance, H00140 (Row 3...)

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Sum Index Match help needed

    Change B2:H2, in Ben's formula to:

    INDEX(B2:H3,MATCH(F9,A2:A3,0),0)

    So finally,

    =SUMIFS(INDEX(B2:H3,MATCH(F9,A2:A3,0),0), B1:H1, ">="&D9, B1:H1, "<"&D9+E9)
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Registered User
    Join Date
    06-11-2013
    Location
    Toronto
    MS-Off Ver
    2013
    Posts
    25

    Smile Re: Sum Index Match help needed

    Awesome! Thank you everyone for the great Help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Index Match help needed
    By SKIDDERWOLF in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 05-14-2013, 04:19 AM
  2. Index match help needed
    By jmanz in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-06-2012, 05:52 AM
  3. Help needed with INDEX MATCH...
    By Nerf Herder in forum Excel General
    Replies: 4
    Last Post: 06-27-2012, 04:40 AM
  4. [SOLVED] Index and Match Help Needed
    By carl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-26-2005, 05:05 PM
  5. Index and match functions help needed.
    By Bob Phillips in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-06-2005, 05:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1