Countif?

[JimS]

5 4 7 1 1
3 8 2 9 6
4 5 6 7 4
3 2 7 8 3


Going across, I want to count the number the number of times column 1
matches column 5. The answer would be two. I tried sumproduct and
countif, but I must be formatting it wrong.

Thanks

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A1:A4=E1:E4))

 

[Shane Devenshire]

Hi,

=SUMPRODUCT(N(A1:A4=E1:E4))

or the array entered equivalent (Shift+Ctrl+Enter to enter it.)

=SUM(N(A1:A4=E1:E4))

 

[JimS]

Thanks, but what if I have blank rows. It seems to be counting those
as matches:

5 4 3 2 5
4 3 2 1 6
3 2 1 4 3


5 6 7 8 9
4 5 6 7 4

 

[Bernard Liengme]

=SUMPRODUCT(--(A1:A5=E1:E5),--(A1:A5<>0))
best wishes

 

[JimS]

Thank you, both.