Here is a formula challenge for you.
![How many values are common in 2 lists [homework]](https://img.chandoo.org/hw/formula-size-of-common-values-in-2-lists.png "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:
- Write the formula if 2 lists have non-numeric values (text for example) ?
- Write the formula if 2 lists are not of same size (A1:A10 and B1:B15 for example) ?
- 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.
78 Responses
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))}
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
I used the following
=SUMPRODUCT(COUNTIF(C1:C10, A1:A10))
The sumproduct avoids having to use CSE to confirm.
= 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
i’m getting zero even i should not
Condition formating
=$G$4:$G$13,$H$4:$H$13
=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.
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)
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)
Grant beat me to it.
I would use this
{=COUNT(MATCH(E9:E17,F9:F17,0))}
Regards,
Saran
http://www.lostinexcel.blogspot.com
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
Good morning,
Interesting formula if E9:E17 contains no duplicate and F9:F17 does. It still gives of number of distinct occurrences.
I really like this. It is simple and works for numerics, text and duplicates. Well Done
=SUM(COUNTIF($A$1:$A$10,B1:B10)) as an array formula.
Hi all,
=IF(COUNTIF(A$2:A$11,B2:B11),”Duplicate”,”Unique”)
Thanks,Nikhil Tripathi
=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)
=if(A=B,1,””)
Sum of above would give you the total
You would need to sort them first.
=SUM(COUNTIF($A$1:$A$10,$B$1:$B$10)) with CSE
OR
=SUMPRODUCT(COUNTIF($A$1:$A$10,$B$1:$B$10))
Regards,
Shweta
http://excelvbatipsforbeginners.blogspot.in/
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.
{index(A:A,SMALL(IF(ISNA(MATCH($A$1:$A:$10,$B$1:$B10,0)),ROW($1:$10),””,ROW(A2)))
=SUM((COUNTIF($D$1:$D$10,C1:C10)>0)*1) as an array formula
Rgds
Jayesh
=COUNT(IF(FREQUENCY(A1:B10,A1:B10)>1,0))
3. =SUM(COUNTIF($A$1:$A$9,B1:B9))+ SUM(COUNTIF($A$1:$A$9,C1:C9))
as an array
I tried this and it works:
=IFERROR(COUNT(MATCH(F6:F15,E6:E15,0)),””) use Ctrl + Shift + Enter.
It is an array formula.
=SUMPRODUCT(ISNUMBER(MATCH(A1:A10,B1:B12,0))*1)
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
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
I tired this one and it worked – entered as ctrl-shift-enter:
{=SUM(–NOT(IFERROR(MATCH(B1:B10,A1:A10,0),0)=0))}
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)
=SUM(COUNTIF($E$6:$E$17,D6:D17)) (array formula)
Count of both the list having common values
{=SUM(COUNTIF(D4:D9,E4:E9)*1,COUNTIF(E4:E9,D4:D9)*1)}
Hi,
I use below formula:
=SUMPRODUCT(COUNTIF(A2:A11,B2:B11))
Better use sumproduct to avoid CSE
Lot Of Answers
Now Please tell which one is convenient???
Hi,
I have used below formula
sumproduct(countif(A1:A6,B2:B10))
I would use
=COUNT(A1:B10)-SUM(IF(FREQUENCY(A1:B10,A1:B10)>0,1))
=IF(COUNTIF($B$3:$B$12,C3)>0,C3&” is common”,”NA”)
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))
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
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.
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)
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
=SUM(COUNTIF(B1:B10,A1:A10)) CSE ( Ctrl + Shift + Enter)
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)
Apologies.
In 3°) please read : SUMPRODUCT(COUNTIF(LIST_B,LIST_A),COUNTIF(LIST_C,LIST_A)) (cse)
(LISt_D replaced by LIST_C)
Works for alpha, numeric and mixed lists and diferent size lists.
=SUMPRODUCT(ISNUMBER(MATCH(A24:A43,B24:B34,0))*1)
=SUM(COUNTIF($A$1:$A$10,$B$1:$B$10))
entered as an array formula.
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
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
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
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}
@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
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”….
For three list, array formula
=SUMPRODUCT(IF(COUNTIF(B1:B10,A1:A10)>0,1,0),IF(COUNTIF(C1:C10,A1:A10)>0,1,0))
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
This should work !!
=SUM(- -ISNUMBER(1/COUNTIF(B1:B10,A1:A10)))
Control Shift Enter (CSE)
It would be double negative instead of the the double negative visible as only a single negative.
@Raj
I added a space between the two -‘s to the original post for clarity
Thanks….getting use to the excel blogs !!
{=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.)
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
=SUM(COUNTIFS(C2:C11,D2:D11))
With CSE
Apply this array formula…
=COUNT(MATCH($A$2:$A$11,$B$2:$B$11,0))
=SUM(COUNTIF($C$1:$C$10,IF(COUNTIF($B$1:$B$10,$A$1:$A$10)>0,$A$1:$A$10,””)))
=SUM(IF(COUNTIF($K$45:$K$49,J45:J49)>0,1,0)) with array passing
=ISNUMBER(MATCH(A1,$B$1:$B$10,0))
={SUM(COUNTIF($A$1:$A$n,B1:Bn))}
{} -> Denotes array formula
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.
This works with lists of varying sizes too.
=SUMPRODUCT(–ISNUMBER(MATCH(A2:A11,B2:B11,0)))
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))}
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.
This allows numeric/text mixed of different lengths and yields the innerjoin count:
=SUMPRODUCT(ISNUMBER(MATCH(B1:B11,D1:D15,0))*1)
=SUMPRODUCT(COUNTIF(List B, List A))
=FILTER(A1#,ISNUMBER(XMATCH(A1#,B1#)))
=COUNT(MATCH(A1:A10,B1:B10,0))