+ Reply to Thread
Results 1 to 5 of 5

Sumif + Index + Match Trouble

  1. #1
    Registered User
    Join Date
    08-13-2013
    Location
    Mankato, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    33

    Sumif + Index + Match Trouble

    Hello,

    Here is my situation:
    (See Picture)

    The three left columns are constantly being added to. The Category 1 and Subcategory 1 columns never change, they are the table of constants. Total Amount- the last column, on the right- needs to show the sum of the amounts that are equal to the Category 1 and Subcategory 1. I currently have the formula: =INDEX($C$19:$C$27,MATCH(D19&E19,$A$19:$A$27&$B$19:$B$27,0)) which gathers the value of the amount, but only takes the first value, not the sum of the values. For instance Financing Partner should be 600 total.

    How do I add Sumif to my current formula to make this happen?
    Attached Images Attached Images
    Last edited by simple?; 08-13-2013 at 03:36 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Sumif + Index + Match Trouble

    Try

    =SUMIFS($C$19:$C$27,$A$19:$A$27,D19,$B$19:$B$27,E19)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Sumif + Index + Match Trouble

    =sumproduct(($a$19:$a$27=d19)*($b$19:$b$27=e19)*(C19:C27))

  4. #4
    Registered User
    Join Date
    08-13-2013
    Location
    Mankato, Minnesota
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Sumif + Index + Match Trouble

    These both work perfectly! Thank you so much, you two make it seem so easy.

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Sumif + Index + Match Trouble

    Glad we could help. 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. Trouble with index/match formula
    By mstar93 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-05-2013, 08:27 PM
  2. [SOLVED] INDEX MATCH Trouble
    By jjislas in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-27-2013, 03:46 PM
  3. Trouble with index and match formula
    By mrggutz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-26-2013, 11:02 PM
  4. [SOLVED] Trouble with Index and match formula
    By mdot218 in forum Excel General
    Replies: 2
    Last Post: 04-03-2012, 10:41 AM
  5. Replies: 3
    Last Post: 04-06-2011, 11:13 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