Results 1 to 6 of 6

SUMIF with INDEX and MATCH?

Threaded View

  1. #1
    Registered User
    Join Date
    05-18-2010
    Location
    Birmingham, UK
    MS-Off Ver
    Excel 2007
    Posts
    16

    Question SUMIF with INDEX and MATCH?

    All,

    I am hoping that you can help me with a Index/Match problem I am having. On the attached spreadhseet is an example of my issue.

    In a nutshell, I want the summary section of the spreadsheet to sum my desired product and desired period detail and bring me back the total for that specific period and product.

    I have to select 2 fields at present:

    CELL B3 - SELECT PERIOD - This is the desired periods budget data I with to retrieve from the source data in A8:O21

    AND

    CELL B5 - SELECT PRODUCT - ALL products are listed in column A and are phased periodically as per the data in A8:O21


    In Row 25 downwards is the summary data that i wish to retrieve from the source data.

    In this example I have selected product D and using an index/match formula is bringing me back the budget data for product D of 2,000 in cell B28.

    The index/match formula is detailed below (please note that i have also included an IFERROR to bring me back 0 instead of N/A):

    =IFERROR(INDEX($C$9:$O$21,MATCH(B5,$A$9:$A$21,0),MATCH(B3,$C$8:$O$8,0))," ")

    It this however giving me the incorrect value as the total budget for period 3 and D is 8,100 (sum of E13:E16).

    How can i get the index/match function to sum all of product D to give me the correct budget for period 3 (or any for the periods 1-13 should I wish to change the period).

    Your help is greatly appreciated.

    PLEASE NOTE THAT I AM USING EXCEL 2010.

    Mr_Vic
    Attached Files Attached Files
    Last edited by mr_vic; 04-22-2013 at 11:19 AM. Reason: Solved

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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