How many values are common in 2 lists? [homework]

Posted on September 14th, 2012 in Excel Challenges , Excel Howtos - 65 comments

Here is a formula challenge for you.

How many values are common in 2 lists [homework]

Lets say we have 2 lists of values in A1:A10 & B1:B10

Now, how do you find the number of common values in both lists?

We just want the count, not list of common values it self.

Go ahead and figure out the formula and post your answers here.

Bonus challenges:

  1. Write the formula if 2 lists have non-numeric values (text for example) ?
  2. Write the formula if 2 lists are not of same size (A1:A10 and B1:B15 for example) ?
  3. Write the formula if we have 3 lists (all same size, numeric values) ?

Go ahead and share your answers. I am eager to see what you will come up with.

PS: For some inspiration, check out this forum discussion.

PPS: If you just want to highlight the common values, see this.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

65 Responses to “How many values are common in 2 lists? [homework]”

  1. Psuken says:

    I guess this would make it : {=SUM(COUNTIF(A3:A12;”=”&B3:B12))}

    It works with both numeric and text values and range B3:B12 can be extended to a third row.

    Note: My excel is in french so I tried to translate functions to english. French formula is  {=SOMME(NB.SI(A3:A12;”=”&B3:B12))}

  2. George says:

    If you have your data in Columns A, B and C, put a helper column in, and sum over it:
     `=IF(AND(ISNUMBER(MATCH(A2,$B:$B,FALSE)),ISNUMBER(MATCH(A2,$C:$C,FALSE))),1,0)`

    It doesn’t matter if you compare to all elements in A, B or C, as if it has to be common over all three then it must appear in that list (that’s why you only need two statements in your AND() formula) – this can be expanded for as many sets of data as you want, and it doesn’t matter about the relative size, as long as you make your helper column as long as column A.  If you were doing this for large sets of data then you’d probably want to replace A with whichever was shortest, but in the example given it doesn’t really matter.

     If you wanted to find out if it’s common in any two of the three rows it gets a little more complicated, but you still use the same basic idea:

    ‘=IF(OR(ISNUMBER(MATCH(A2,B:B,FALSE)),ISNUMBER(MATCH(A2,C:C,FALSE)),ISNUMBER(MATCH(B2,C:C,FALSE))),1,0)’

    In this case, you’re doing the same basic operation as above, but by replacing the AND() statement with an OR(), and adding in a condition to say “check B against C”. This time you have to make your helper column as long as the second shortest of A, B and C.

    When it comes to conditional formatting, you’d use the following:

    ‘=IF(AND(ISNUMBER(MATCH(A2,B:B,FALSE)),ISNUMBER(MATCH(A2,C:C,FALSE))),TRUE,FALSE)’
    To highlight if it’s common over all three (in A), juggle about for B and C,
    and
    ‘=IF(OR(ISNUMBER(MATCH(A2,B:B,FALSE)),ISNUMBER(MATCH(A2,C:C,FALSE))),TRUE,FALSE)’
    to highlight if it’s common over A and B or A and C.

    Hopefully the explanations make sense, and the formalae work as they look like they do to me,

    George 

  3. Grant says:

    I used the following

    =SUMPRODUCT(COUNTIF(C1:C10, A1:A10))

    The sumproduct avoids having to use CSE to confirm.  

  4. Unai says:

    = SUMPRODUCT( –NOT(ISERROR(MATCH(ListA; ListB; 0))) )
    = SUMPRODUCT( –NOT(ISERROR( MATCH(ListA; ListB; 0) * MATCH(ListA; ListC; 0) )) )

    And the lists can be of different size.

    Congratulations for your excellent blog, Chandoo. I learn (and enjoy) a lot reading it. I must confess, however, that I am in Daniel Ferry’s Excel Hero Academy 3, which has inspired this solution to your challenge…

    Unai

  5. Jaywant Nakti says:

    Condition formating
    =$G$4:$G$13,$H$4:$H$13

  6. Tom C says:

    =sum(–not(isna(match(ListA, ListB, 0))))
     
    I don’t think it matters if the lists are different sizes – I used ISNA instead of ISERROR so that it only caught the missing matches, and not any other errors.
     

  7. Chiquitin says:

    Assuming that first value of List A is in A1, first value of list B is in B1 and first value of list C is in C1, and in order to avoid repeated values in any list, we can use:

    {=SUM(IF(COUNT.IF(ListA,ListB)>0,1,0)*IF(COUNT.IF(ListC,ListB)>0,1,0))}

    Defining
    ListA=OFFSET($A$1;;;COUNTA($A:$A);1)
    ListB=OFFSET($B$1;;;COUNTA($B:$B);1)
    ListC=OFFSET($C$1;;;COUNTA($C:$C);1)

    • Chiquitin says:

      Sorry, but in Spanish excel we use semicolon instead comma.

      ListA=OFFSET($A$1,,,COUNTA($A:$A),1)
      ListB=OFFSET($B$1,,,COUNTA($B:$B),1)
      ListC=OFFSET($C$1,,,COUNTA($C:$C),1)

  8. Eric says:

    Grant beat me to it.

  9. Saran says:

    I would use this

    {=COUNT(MATCH(E9:E17,F9:F17,0))}

    Regards,
    Saran
    http://www.lostinexcel.blogspot.com

  10. Michael says:

    =SUM(COUNTIF($A$1:$A$10,B1:B10)) as an array formula.

  11. Nikhil Tripathi says:

    Hi all,

    =IF(COUNTIF(A$2:A$11,B2:B11),”Duplicate”,”Unique”)

    Thanks,Nikhil Tripathi

  12. Fred C says:

    =COUNTIF($B$2:$B$11,A2)+COUNTIF($B$2:$B$11,A3)+COUNTIF($B$2:$B$11,A4)+COUNTIF($B$2:$B$11,A5)+COUNTIF($B$2:$B$11,A6)+COUNTIF($B$2:$B$11,A7)+COUNTIF($B$2:$B$11,A8)+COUNTIF($B$2:$B$11,A9)+COUNTIF($B$2:$B$11,A10)++COUNTIF($B$2:$B$11,A11)

  13. =if(A=B,1,””)
    Sum of above would give you the total

  14. Shweta Jain says:

    =SUM(COUNTIF($A$1:$A$10,$B$1:$B$10)) with CSE

  15. Kyle McGhee says:

    I will say
    =SUM(IF(Uniques=1,0,1/(Uniques))) (CSE)
    where the named formula “Uniques” is:
    “=COUNTIF(Sheet1!$A$1:$B$10,Sheet1!$A$1:$B$10)” 
    The name is not necessary, just for presentation purposes.
    This method should cover bonus challenges 1 and 3 as well.

  16. Sohail Rizki says:

    {index(A:A,SMALL(IF(ISNA(MATCH($A$1:$A:$10,$B$1:$B10,0)),ROW($1:$10),””,ROW(A2)))
     

  17. Jayesh Padhya says:

    =SUM((COUNTIF($D$1:$D$10,C1:C10)>0)*1) as an array formula

    Rgds
    Jayesh 

  18. Virginia says:

    =COUNT(IF(FREQUENCY(A1:B10,A1:B10)>1,0))

  19. JimmyG says:

    3. =SUM(COUNTIF($A$1:$A$9,B1:B9))+ SUM(COUNTIF($A$1:$A$9,C1:C9))

    as an array 

  20. I tried this and it works:

    =IFERROR(COUNT(MATCH(F6:F15,E6:E15,0)),””) use Ctrl + Shift + Enter.
    It is an array formula.   

  21. shrivallabha says:

    =SUMPRODUCT(ISNUMBER(MATCH(A1:A10,B1:B12,0))*1)

  22. Rajan says:

    In this world of emails, have you got bogged down when you require to send same Email to no. of recepients with minor changes…
     
    It would have taken lot of time and effort to email same matter to different email addresses
    Here comes our solution to this issue.
     
    http://exceltutor.org/index.php?option=com_content&view=article&id=156&Itemid=142

  23. Detlef says:

    Hi
    =SUMPRODUCT(COUNTIF(ListA,ListB))
    Same formula works for bonus questions 1 and 2.
     
    Bonus question 3:
    =SUMPRODUCT(–(COUNTIF(A1:C10,A1:C10)=3))/3
     

  24. kazshak says:

    I tired this one and it worked – entered as ctrl-shift-enter:

    {=SUM(–NOT(IFERROR(MATCH(B1:B10,A1:A10,0),0)=0))}

  25. Matthew Holbrook says:

    For 2 lists, same size: =SUMPRODUCT(COUNTIF(A1:A11,B1:B11))
    or =SUM(COUNTIF(A2:A11,B2:B11)) (array formula)
    or =SUM(IF(COUNTIF(A1:A11,B1:B11)>0,1,0)) (array formula)

    For 2 lists, different sizes: =SUM(COUNTIF(H2:H11,I2:I16)) (array formula)

    For 3 lists, same size: =SUM(IF(COUNTIF(B16:C25,A16:A25)>1,1,0)) (array formula)

  26. VInay says:

    =SUM(COUNTIF($E$6:$E$17,D6:D17)) (array formula)

  27. rajinikanth says:

    Count of both the list having common values
    {=SUM(COUNTIF(D4:D9,E4:E9)*1,COUNTIF(E4:E9,D4:D9)*1)}

  28. Manik Nandi says:

    Hi,

    I use below formula:

    =SUMPRODUCT(COUNTIF(A2:A11,B2:B11))

    Better use sumproduct to  avoid CSE

  29. Waqas says:

    Lot Of Answers
    Now Please tell which one is convenient??? 

  30. Gaurav Bansal says:

    Hi,
    I have used below formula
    sumproduct(countif(A1:A6,B2:B10))

  31. Amathya says:

    I would use
    =COUNT(A1:B10)-SUM(IF(FREQUENCY(A1:B10,A1:B10)>0,1))
     

  32. Manju.N says:

    =IF(COUNTIF($B$3:$B$12,C3)>0,C3&” is common”,”NA”)

  33. nazmul_muneer says:

    It is considered, in the COLUMN A or COLUMN B duplicate value may be typed.
    At first we have find out distinct value in both columns

    =SUMPRODUCT(COUNTIF($A$1:$A$10,B1:B10)*COUNTIF($B$1:$B$10,A1:A10))

  34. Kaushik says:

    1) Number of common values in both lists(List A and List B):[List A and List B are headers...data starts from A2 and B2 , respectively.]
    =SUMPRODUCT(COUNTIF(B2:B11,A2:A11))    (ctrl+shift+enter)
    2) Formula if 2 lists have non-numeric values (text for example)
    Same formula works in this case as well
    =SUMPRODUCT(COUNTIF(B2:B11,A2:A11))    (ctrl+shift+enter)
    3) 2 lists are not of same size (A1:A10 and B1:B15 for example)
    Same above formula works but only the reference needs to be changed:
    SUMPRODUCT(COUNTIF(B2:B15,A2:A11))    (ctrl+shift+enter)
    4) Formula if we have 3 lists (all same size, numeric values)
    Along with your list A and List B data , say List C contains: 50,12,69,25,60,30,10,58,21,47 (from C2 to C11):
    Formula would be:
    SUMPRODUCT(COUNTIF(B2:B11,A2:A11))+SUMPRODUCT(COUNTIF(C2:C11,A2:A11))
    (ctrl+shift+enter)
    should return 9
    Regards,
    Kaushik

    • Jeanbar says:

      Kaushik,
      About your 4)
      The answer shouldn’t be 9 with the data you provided but 3. The common elements between all tables are 30,50,60.
      On top of this, why do you use SUMPRODUCT when you have only one table e.g. COUNTIF(B2:B11,A2:A11) (cse)? a simple SUM would suffice.

  35. NARENDRA says:

    List A

     

    List B

     

    10

     

    11

     

    20

     

    20

     

    30

     

    30

     

    40

     

    33

     

    50

     

    40

     

    60

     

    45

     

    70

     

    50

     

    80

     

    60

     

    90

     

    78

     

    100

     

    99

     

    =IF(COUNTIF($B$2:$B$11,A2),A2,0)

  36. Manoj says:

    10
    11
    =IFERROR(VLOOKUP(C2,$D$2:$D$11,1,FALSE),0)
    0

    20
    20
    =IFERROR(VLOOKUP(C3,$D$2:$D$11,1,FALSE),0)
    20

    30
    30
    =IFERROR(VLOOKUP(C4,$D$2:$D$11,1,FALSE),0)
    30

    40
    33
    =IFERROR(VLOOKUP(C5,$D$2:$D$11,1,FALSE),0)
    40

    50
    40
    =IFERROR(VLOOKUP(C6,$D$2:$D$11,1,FALSE),0)
    50

    60
    45
    =IFERROR(VLOOKUP(C7,$D$2:$D$11,1,FALSE),0)
    60

    70
    50
    =IFERROR(VLOOKUP(C8,$D$2:$D$11,1,FALSE),0)
    0

    80
    60
    =IFERROR(VLOOKUP(C9,$D$2:$D$11,1,FALSE),0)
    0

    90
    78
    =IFERROR(VLOOKUP(C10,$D$2:$D$11,1,FALSE),0)
    0

    100
    99
    =IFERROR(VLOOKUP(C11,$D$2:$D$11,1,FALSE),0)
    0

     
     
    =COUNTIF(E2:E11,”>0″)
    5

  37. Vinay Saraf says:

    =SUM(COUNTIF(B1:B10,A1:A10))        CSE ( Ctrl + Shift + Enter)

  38. Jeanbar says:

    My answer, assuming there is no duplicate in either lists:
    1°) Write the formula if 2 lists have non-numeric values (text for example) ?
    SUM(COUNTIF(LIST_B,LIST_A)) (cse) or SUM(COUNTIF(LIST_A,LIST_B)) (cse)
    2°) Write the formula if 2 lists are not of same size (A1:A10 and B1:B15 for example) ?
    same as above
    3°) Write the formula if we have 3 lists (NOT all same size, NON-numeric values) ?
    I have 3 lists of different sizes LIST_A; LIST_B; LIST_C containing non-numeric values:
    SUMPRODUCT(COUNTIF(LIST_B,LIST_A),COUNTIF(LIST_D,LIST_A)) (cse)

    • Jeanbar says:

      Apologies.
      In 3°) please read : SUMPRODUCT(COUNTIF(LIST_B,LIST_A),COUNTIF(LIST_C,LIST_A)) (cse)
      (LISt_D replaced by LIST_C)

  39. Oli says:

    Works for alpha, numeric and mixed lists and diferent size lists.

    =SUMPRODUCT(ISNUMBER(MATCH(A24:A43,B24:B34,0))*1)

  40. Alok Joshi says:

    =SUM(COUNTIF($A$1:$A$10,$B$1:$B$10))
     entered as an array formula. 

  41. magbo says:

    array formula for two lists (either numeric or text values):
    =SUM(–(($A$1:$A$10)=TRANSPOSE($B$1:$B$15))) 

    and for three lists:
    =SUM((1-ISERROR(MATCH($A$1:$A$10,TRANSPOSE($B$1:$B$11),0)))*(1-ISERROR(MATCH($A$1:$A$10,TRANSPOSE($C$1:$C$8),0))))

    Both formulas should work if lists do not have duplicates
     

  42. magbo says:

    BTW: easy way to “translate” excel formulas between different language versions is:
    – select a cell with formula
    – open VBA Editor
    – open an Immediate Window
    – type: ?activecell.Formula
    – and press Enter

    And if you have local (not english) version of excel – again 

    – select a cell 
    – open VBA Editor
    – open an Immediate Window
    – type: activecell.Formula = ” copy english formula
    – and press Enter

  43. Akash Khandelwal says:

    I have a single formula which can be used in all these cases :

    1. ” =SUMPRODUCT(COUNTIF(A1:A10,B1:B10)) ”
                   Works with both Numeric and Text Value.

    2. ” =SUMPRODUCT(COUNTIF(A1:A10,B1:B15)) ”
                    Just a version of previous formula with changed range.

    3. “=SUMPRODUCT(COUNTIF(A1:A10,C1:C10),COUNTIF(B1:B10,C1:C10))”
                    Extended version of the First Formula, does well with any no. of columns and range.

    :)

    Rgds,
    Akash Khandelwal 

  44. [...] Re: is this a match ? Hi Coincidently there has been a posting with a similar question at chandoo.org. Check it out. How many values are common in 2 lists? [homework] | Chandoo.org – Learn Microsoft Excel Online [...]

  45. Pablo says:

    This formula array worked, but I don’t really know why the division by 2. Maybe it’s a case for some Forensics?

    {=SUM((COUNTIF(A1:B10,A1:B10)>1)*1)/2}

  46. Yasmeen says:

    Simplest Way is select the 2 column of list a and list b go to conditional formatting and use duplicate values it will highlight u duplicate values which is in both the columns ..”no formula required”….

  47. Amritansh says:

    For three list, array formula
    =SUMPRODUCT(IF(COUNTIF(B1:B10,A1:A10)>0,1,0),IF(COUNTIF(C1:C10,A1:A10)>0,1,0))
     

  48. Rajesh Kumar says:

    I have to use conditional formatting.
    step1:I have to select total data wher we want change colour.
    step2:then i have to go in conditional formating & select new rule .
    step3: select Use a formuleto determine which cells to formate.
    step4:Type formule =countif(select unique number column,first cell of selected  data)=1
    step5: fill format as you like

  49. Raj Kumar Kothari says:

    This should work  !!
     
    =SUM(- -ISNUMBER(1/COUNTIF(B1:B10,A1:A10)))
     
    Control Shift Enter (CSE)

  50. Joe Farrell says:

    {=COUNTIF(ISERROR(INDEX($A$1:$B$11;MATCH(A1:A11;$B$1:$B$11;0);2));0)}

    Did this array formula in Libre Office Calc. (Uses semicolons as variable separators.)

  51. teatourist says:

    Place the following in a helper column (E) after the three lists are in columns B,C&D:

    =IF(IFERROR(MATCH(B13,$C$3:$C$5,0),””)=””,””,IFERROR(MATCH(B13,$D$12:$D$14,0),””))

    Then just do a COUNT of column E

  52. Brij Arora says:


    =SUM(COUNTIFS(C2:C11,D2:D11))

    With CSE

  53. MAHESH C BHATT says:

    Apply this array formula…
    =COUNT(MATCH($A$2:$A$11,$B$2:$B$11,0))

Leave a Reply