+ Reply to Thread
Results 1 to 5 of 5

Sumifs/Index formula - no result

  1. #1
    Registered User
    Join Date
    01-02-2014
    Location
    Cape Town
    MS-Off Ver
    Excel 2007
    Posts
    88

    Sumifs/Index formula - no result

    Hi
    I'm trying to sum information from a different worksheet with formula
    =SUMIFS($H$3:$H$10,$C5,$K$3:$K$10,$E$12,INDEX($H$2:$L$10,0,,MATCH($E$1,$I$1:$L$1,+COLUMNS($E5:$E5-1))))
    Pls can you help as it seems I'm doing something wrong
    results in E5 should be 100% and in F5 0.07%

    Many thanks
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumifs/Index formula - no result

    not sure what you are trying to do but the first range in a SUMIFS formula is the range with the numbers you want to sum, then the criteria range1 and criteria1, criteria range2 and criteria2, etc.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    01-02-2014
    Location
    Cape Town
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Sumifs/Index formula - no result

    Okay maybe I'm totally on the wrong track but if you look at my example workbook I would like the results in column F5 to be 0.07% (which is in column L3...if this helps

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Sumifs/Index formula - no result

    for what you posted and your reply in post #3, this would be enough... =INDEX(L3:L10,MATCH(C5,H3:H10,0))
    though I suppose there is more to this than I've seen so far.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Sumifs/Index formula - no result

    First it doesn't appear you need SUMIFS. SUMIF will suffice.

    Second the syntax of the formula needs attention.

    =SUMIFS($H$3:$H$10,$C5,$K$3:$K$10,$E$12,INDEX($H$2:$L$10,0,,MATCH($E$1,$I$1:$L$1,+COLUMNS($E5:$E5-1))))

    This syntax of INDEX has 4 arguments. If you are not aware of it the MATCH is invoking the 4th argument (Area) that does not apply here.
    Remove the 0, from INDEX($H$2:$L$10,0,,MATCH($E$1,$I$1:$L$1,+COLUMNS($E5:$E5-1)))

    Third the MATCH function is expecting a match type in the 3rd argument. Try replacing +COLUMNS($E5:$E5-1) with 0.

    Fourth the range H$2:$L$10 starts in row 2. The criteria range $H$3:$H$10 starts in row 3. Make both those the same --- preferably 2 but 3 should work as well.

    Fifth it appears the date row is irrelevant. If not and something else is to be considered the same that holds for point four holds for the match range $I$1:$L$1. The index range is in columns H:L. Make those the same --- both I or H.

    Sixth dispense with the date references and apply the % and L/G-Thruput criteria to the range H2:L2 in the MATCH function.


    Please try in E5 filled down and across and see the attached.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

+ 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. [SOLVED] Pls help with a sumifs index match formula
    By Roma1r in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2019, 06:01 AM
  2. [SOLVED] to show only one result per date for sumifs formula
    By LFKim2018 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-05-2018, 09:27 PM
  3. [SOLVED] Sumifs formula not showing correct result, one of criteria is from pivot table
    By Ishwarind in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2017, 07:59 AM
  4. [SOLVED] Formula Sumifs with Index Match not working
    By vba1234 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-15-2016, 06:37 AM
  5. Need help with Sumifs, Index, and match formula!
    By relmasri in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2016, 10:27 AM
  6. [SOLVED] SUMIFS Formula not giving desired result
    By lukemelville in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-12-2013, 09:15 AM
  7. SUMIFS Puzzle - Trying to avoid adding multiple SUMIFS to get valid result
    By haldavid in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-09-2013, 03:42 PM

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