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

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

HTH,
Bernie
MS Excel MVP


"Medikto D" <Medikto [email protected]> wrote in message
news:[email protected]...
> 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

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

>
>

Maybe this is more intuitive....

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

Entered using Ctrl-Shift-Enter

HTH,
Bernie
MS Excel MVP


"Medikto D" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>>
>>