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

Share

Here is a formula challenge for you. 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.

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.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### How to learn Python as an “Excel person”?

Python is a great programming language to learn for anyone in data profession. For people with prior Excel skills and a little bit of programming bent, Python

## Related Tips

### 77 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

• Excel101 says:

i'm getting zero even i should not

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

• Saran says:

Assuming the 1st set of data is in E9:E17 and Second set of data is F9:F17

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

• Jeanbar says:

Good morning,
Interesting formula if E9:E17 contains no duplicate and F9:F17 does. It still gives of number of distinct occurrences.

• Linda says:

I really like this.  It is simple and works for numerics, text and duplicates.  Well Done

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. Jo Anne Jordan says:

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

• Jo Anne Jordan says:

You would need to sort them first.

14. Shweta Jain says:

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

• Shweta Jain says:

OR

=SUMPRODUCT(COUNTIF(\$A\$1:\$A\$10,\$B\$1:\$B\$10))

Regards,
Shweta
http://excelvbatipsforbeginners.blogspot.in/

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. Mohammed Mustafa says:

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:

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,
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.
(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}

• Hui... says:

@Pablo
if there is a duplicate there will be two copies of each value hence the /2 part.
it may not work if there is an odd number of multiples

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)

• Raj Kumar Kothari says:

It would be double negative instead of the the double negative visible as only a single negative.

• Hui... says:

@Raj
I added a space between the two -'s to the original post for clarity

• Raj Kumar Kothari says:

Thanks....getting use to the excel blogs !!

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))

54. Daniel says:

=SUM(COUNTIF(\$C\$1:\$C\$10,IF(COUNTIF(\$B\$1:\$B\$10,\$A\$1:\$A\$10)>0,\$A\$1:\$A\$10,"")))

55. rahul saha says:

=SUM(IF(COUNTIF(\$K\$45:\$K\$49,J45:J49)>0,1,0)) with array passing

56. Nishant says:

=ISNUMBER(MATCH(A1,\$B\$1:\$B\$10,0))

57. kaushik says:

={SUM(COUNTIF(\$A\$1:\$A\$n,B1:Bn))}
{} -> Denotes array formula

58. adivyom says:

I created a parallel column C, applied the following formula in C1 and dragged it till C10:

=--AND(COUNTIF(\$A\$1:A1,A1)=1,COUNTIF(\$B\$1:\$B\$10,A1)>0)

Sum of '1s' in C1:C10 is the no. of values that are common between A1:A10 and B1:B10.

59. PINOY-exceler says:

This works with lists of varying sizes too.

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A11,B2:B11,0)))

60. Corne says:

This works for varying sizes and also only counts unique occurrences:

{=SUM(--(FREQUENCY(IF(ISNA(MATCH(B2:B20,C2:C20,0)),0,MATCH(B2:B20,C2:C20,0)),IF(ISNA(MATCH(B2:B20,C2:C20,0)),0,MATCH(B2:B20,C2:C20,0)))>0))}

61. Yves S says:

Q1-Q3:
={SUMPRODUCT(COUNTIF(A2:An,B2:Bm))}
works with different list sizes.

Q4:
={SUMPRODUCT(COUNTIF(A2:An,C2:Cn),COUNTIF(B2:Bn,C2:Cn))}

Criteria range must be same in both arguments. I tested permuting A, B, C. If criteria range is different it won't work.

62. cnestg8r says:

This allows numeric/text mixed of different lengths and yields the innerjoin count:

=SUMPRODUCT(ISNUMBER(MATCH(B1:B11,D1:D15,0))*1)

63. RAJIV SIROHI says:

=SUMPRODUCT(COUNTIF(List B, List A))

64. Anonymous Coward says:

=FILTER(A1#,ISNUMBER(XMATCH(A1#,B1#)))

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.