counting line by line matches of 2 columns possible in excel ?
 

result guy1 guy2
duitsland-costa rica 1 1 1 1 1
polen- ecuador 1 X X X 1
engeland-paraguay 2 1 1 1 1 X
trinidad-zweden 2 2 2 2 1 2
argentinië-ivoorkust 1 1 1 1 1 1
servië-nederland 2 X 2 2 2 2
mexico-iran 2 1 1 1 1 1
angola-portugal 2 2 2 2 2
italië-ghana x 1 1 1 1 1
usa-tsjechië 1 2 X 2 2 X
brazilië-kroatië 1 1 1 1 1 1
australië-japan 1 2 2 2 X 1
frankrijk-zwitserland 1 1 1 1 X
zuid korea-togo 1 1 X 2 X
formula needed
is counting line by line matches of 2 columns even possible in excel
,without the long
=SUM(IF($B2=C2;1;0);IF($B3=C3;1;0);IF($B4=C4;1;0); IF($B5=C5;1;0);IF($B6=C6;1;0)) ...lotsa matches

counting line by line matches of 2 columns possible in excel ?
 

=SUMPRODUCT(--(B2:B100=C2:C100))

HTH,
Bernie
MS Excel MVP


"Medikto D" <Medikto wrote in message
...
result guy1 guy2
duitsland-costa rica 1 1 1 1 1
polen- ecuador 1 X X X 1
engeland-paraguay 2 1 1 1 1 X
trinidad-zweden 2 2 2 2 1 2
argentinië-ivoorkust 1 1 1 1 1 1
servië-nederland 2 X 2 2 2 2
mexico-iran 2 1 1 1 1 1
angola-portugal 2 2 2 2 2
italië-ghana x 1 1 1 1 1
usa-tsjechië 1 2 X 2 2 X
brazilië-kroatië 1 1 1 1 1 1
australië-japan 1 2 2 2 X 1
frankrijk-zwitserland 1 1 1 1 X
zuid korea-togo 1 1 X 2 X
formula needed
is counting line by line matches of 2 columns even possible in excel
,without the long
=SUM(IF($B2=C2;1;0);IF($B3=C3;1;0);IF($B4=C4;1;0); IF($B5=C5;1;0);IF($B6=C6;1;0)) ...lotsa matches

counting line by line matches of 2 columns possible in excel ?
 

Thanks for the fast reply
I looked at all possible solutions even this sumproduct,but you got to admit
it's not the obvious choice,a math function instead of lookup,compare,count
and such.

"Bernie Deitrick" wrote:

=SUMPRODUCT(--(B2:B100=C2:C100))

HTH,
Bernie
MS Excel MVP

counting line by line matches of 2 columns possible in excel ?
 

Maybe this is more intuitive....

=SUM((B2:B100=C2:C100)*1)

Entered using Ctrl-Shift-Enter

HTH,
Bernie
MS Excel MVP


"Medikto D" wrote in message
...
Thanks for the fast reply
I looked at all possible solutions even this sumproduct,but you got to admit
it's not the obvious choice,a math function instead of lookup,compare,count
and such.

"Bernie Deitrick" wrote:

=SUMPRODUCT(--(B2:B100=C2:C100))

HTH,
Bernie
MS Excel MVP