Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Combining Match and SUMIF

  1. #1
    Join Date
    4th January 2005
    Location
    Wellington, New Zealand
    Posts
    136

    Combining Match and SUMIF

    Hopefully this is something that has been done before, although I couldn't find it in searching this morning.

    I need to do a SUMIF on a selection of data where the range is in column J, and the sum range is columns K through BB depending on another criteria (month) which is held in another cell. What I want to do is embed a MATCH statement into a SUMIF, or use some other formula that achieves the same result.

    Here's how it looks as a formula:

    =SUMIF(RetData!$J:$J,$C16,Match($E$5,RetData!$J$5:$BB$5),False))

    Any suggestions? Hopefully the description and the formula show what I am trying to achieve.

    Paul

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,755

    Re: Combining Match and SUMIF

    Hi klxracer,

    Have a look at the example in the workbook attached. It uses a Dynamic Named Range called Values, which in turn uses the Match function to find the column to use.

    The months are a static range called Months and the data is another Dynamic Named Range called Data. By using both of these two as DNRs it ensures that the value ranges for the SumIf are the same.

    The formula in D1 is then just a straightforward SumIf using the range names.

    Hope this helps.

    Regards,
    Batman.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    4th July 2004
    Location
    Canada
    Posts
    2,295

    Re: Combining Match and SUMIF

    Assuming that J5:BB15 contains your data, and that J5:BB5 contains your headers/labels, try the following...

    =SUMIF(RetData!$J$6:$J$15,$C16,INDEX(RetData!$K$6:$BB$15,0,MATCH($E$5,RetData!$K$5:$BB$5,0)))

    See the attached file.

    Hope this helps!
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    4th January 2005
    Location
    Wellington, New Zealand
    Posts
    136

    Re: Combining Match and SUMIF

    Thanks guys for your help - looks as though the problem is solved! Now I just need to replicate for the 8 different IF driven scenario's, quite comfortable with that though.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    9th February 2011
    Posts
    11

    Re: Combining Match and SUMIF

    Hi,

    I couldn't replicate the column lookup for sum_range in a sumifs formula. Any help?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Combining Match and SUMIF

    Hello ally.mateo,

    Posting your question in threads started by others is a violation of the Forum Rules and is known as thread hijacking. Posting solutions is acceptable.

    ALWAYS start a new thread for your questions. You may, if you find it helpful, provide a link back to this (or any other) thread.

    Start a new thread and be sure to give it a title that complies with the following guidelines:

    Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your thread content or overall objective using ONLY search friendly key words.

    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (colons, semi-colons, slashes, etc.)
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. SUMIF - Excel 2007 SUMIF - How to Use Excel SUMIF
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 9th, 2008, 19:48
  2. Sumif Match
    By Andybirks in forum EXCEL HELP
    Replies: 5
    Last Post: October 17th, 2005, 06:11
  3. match 3 cells, average cell based on match and copy
    By diverdls in forum EXCEL HELP
    Replies: 7
    Last Post: September 10th, 2005, 13:38
  4. Combining 2 files with match Student number
    By lyambor in forum EXCEL HELP
    Replies: 4
    Last Post: October 26th, 2004, 12:25
  5. Replies: 6
    Last Post: February 25th, 2004, 22:20

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
  •  
porno