+ Reply to Thread
Results 1 to 10 of 10

Match two lists and sum

Hybrid View

  1. 07-07-2011, 04:18 AM #1
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Match two lists and sum

    Hi all

    I want to compare a list with another list and need the sum from the second column of the second list where the values in both list and columns match.

    I try sumproduct but it does not give the answer.

    I need the answer directly in a cell with vlookup or match function

    The sample file is attached

    Thanks in advance to provide any formula
    Attached Files Attached Files
    Last edited by Azam Ali; 07-13-2011 at 02:39 AM.
    Reply With QuoteRegister To Reply
  2. 07-07-2011, 06:10 AM #2
    estige
    estige is offline
    Valued Forum Contributor
    Join Date
    10-06-2008
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    365

    Re: Match two lists and sum

    Hi, since no one has come up with anything yet, here is a solution that will give you the sum you want in one formula. (G19 in attached file) It is however unelegant and nonflexible, I'm afraid.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 07-07-2011, 06:38 AM #3
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Match two lists and sum

    dear estige

    thanks for your time but this formula is not useable. the cell # E6 is also not taken in your formula.

    i dont want to compare by selecting every cell rather i want to compare two arrays
    Reply With QuoteRegister To Reply
  4. 07-07-2011, 07:05 AM #4
    estige
    estige is offline
    Valued Forum Contributor
    Join Date
    10-06-2008
    Location
    Norway
    MS-Off Ver
    2010
    Posts
    365

    Re: Match two lists and sum

    Yes, I'm sorry. I gave it a go since you suggested using vlookup yourself. You are right that I missed E6. Because of that I didn't catch the problem with the text value. This could be solved, but you are probably right that it would be far better to find some sort of array solution.
    Reply With QuoteRegister To Reply
  5. 07-07-2011, 07:44 AM #5
    Palmetto
    Palmetto is offline
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: Match two lists and sum

    I tried many variations of array formula, but could not get any to return the correct results (I'm sure someone else may get it right, but it evades me for now).

    This can be done using a helper column.
    In cell C1: =AND(COUNTIF($E$3:$E$7,A1),ISNUMBER(B1))

    Where E3:E7 are cells holding the array of values.

    The formula will return TRUE or FALSE.

    Now we simply use a simple SUMIF formula: =SUMIF(C1:C26,TRUE,B1:B26)
    Palmetto

    Do you know . . . ?

    You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
    Reply With QuoteRegister To Reply
  6. 07-07-2011, 08:08 AM #6
    nawas
    nawas is offline
    Forum Contributor
    Join Date
    05-04-2009
    Location
    ME
    MS-Off Ver
    Excel 2003,2007
    Posts
    157

    Re: Match two lists and sum

    Another option on this, since you were trying with Sumproduct

    =SUMPRODUCT(--(A1:A26=E3)--(A1:A26=E4)--(A1:A26=E5)--(A1:A26=E6)--(A1:A26=E7),B1:B26)
    Reply With QuoteRegister To Reply
  7. 07-07-2011, 05:55 PM #7
    highbarger
    highbarger is offline
    Registered User
    Join Date
    05-28-2009
    Location
    Springfield, Missouri, USA
    MS-Off Ver
    Excel 2007
    Posts
    33

    Re: Match two lists and sum

    Try this ARRAY formula. Confirm with Control+Shift+Enter

    =SUM(IF(ISNUMBER(B1:B26),(B1:B26),0)*ISNUMBER(MATCH(A1:A26,E3:E7,0)))
    Reply With QuoteRegister To Reply
  8. 07-07-2011, 05:59 PM #8
    Haseeb Avarakkan
    Haseeb Avarakkan is offline
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    365
    Posts
    2,243

    Re: Match two lists and sum

    Hello Azam,

    Use this one;

    =SUMPRODUCT(SUMIF(A:A,E3:E7,B:B))
    Regards,
    Haseeb Avarakkan
    __________________________________
    "Feedback is the breakfast of champions"
    Reply With QuoteRegister To Reply
  9. 07-12-2011, 07:24 AM #9
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Match two lists and sum

    Thanks so much too all. i have a lot of learning through this thread.

    thanks again to all.
    Reply With QuoteRegister To Reply
  10. 07-13-2011, 02:38 AM #10
    Azam Ali
    Azam Ali is offline
    Valued Forum Contributor
    Join Date
    05-23-2011
    Location
    Lahore PK
    MS-Off Ver
    Excel 2007, 2013
    Posts
    627

    Re: Match two lists and sum

    Hi Haseeb

    The formula is working perfectly. but i dont understand the logic. could you please refer me any tutorials where could i learn it in detail and similar fucntions

    many thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1