+ Reply to Thread
Results 1 to 4 of 4

Sumifs match index

  1. #1
    Registered User
    Join Date
    01-07-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    5

    Sumifs match index

    Hi

    I need to add a sumifs criteria to a match index formula to list unique values from a column.

    unique.JPG

    with formula in D2:
    Please Login or Register  to view this content.
    I don't want the unique list to report the number 44 since the sum of amount with the number 44 is 0 (1-1=0). How would you solve this?
    Attached Images Attached Images

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs match index

    Try this...

    Data Range
    A
    B
    C
    D
    1
    Number
    Amount
    ------
    Unique List
    2
    43
    2
    43
    3
    43
    1
    45
    4
    44
    1
    #N/A
    5
    44
    -1
    6
    45
    2
    7
    45
    2


    This array formula** entered in D2 and copied down:

    =INDEX($A$2:$A$7;MATCH(0;IF(SUMIFS(B$2:B$7;A$2:A$7;A$2:A$7)>0;COUNTIF($D$1:D1;$A$2:$A$7));0))

    If you want to suppress the #N/A errors use this array** version:

    =IFERROR(INDEX($A$2:$A$7;MATCH(0;IF(SUMIFS(B$2:B$7;A$2:A$7;A$2:A$7)>0;COUNTIF($D$1:D1;$A$2:$A$7));0));"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    01-07-2013
    Location
    Norway
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: Sumifs match index

    Thanx Tony, perfectly nested.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Sumifs match index

    You're welcome. Thanks for the feedback!

+ 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] Index Match Sumifs Maybe?
    By TreeLife in forum Excel General
    Replies: 8
    Last Post: 08-29-2016, 11:03 AM
  2. [SOLVED] Help With SUMIFS / INDEX / MATCH
    By jpetrini888 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2016, 10:12 AM
  3. Sumifs/Index/Match
    By ravidesai in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2016, 02:52 PM
  4. [SOLVED] SUMIFS with AND & INDEX & MATCH
    By pdalal in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-29-2014, 02:30 PM
  5. [SOLVED] Sumifs/match/index?
    By mstoto in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-14-2014, 09:23 AM
  6. [SOLVED] Index/Match, SUMIFS or something else? NEED HELP!
    By MMLBaylor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-20-2014, 11:52 AM
  7. SumIFS or Index Match Help
    By excelnovice936 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2013, 12:08 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