A column is having some data and i want to check how many data is repeated
in column B .
I will briefly try to explain it as follows.

A B
a a
b c
c e
d h
e i
f o
g b
h x

Here i am expecting answer as 5 as a,b,c,e and h are common in both columns.

H S Shastri

=SUM(COUNTIF(B:B,A1:A8))

This is an array function that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.
--
Gary''s Student - gsnu200830


"HARSHAWARDHAN. S .SHASTRI" wrote:


A column is having some data and i want to check how many data is repeated
in column B .
I will briefly try to explain it as follows.

A B
a a
b c
c e
d h
e i
f o
g b
h x

Here i am expecting answer as 5 as a,b,c,e and h are common in both columns.

H S Shastri

Hi

One way
=SUMPRODUCT(--(COUNTIF(A1:A9,B1:B9)=1))

--
Regards
Roger Govier

"HARSHAWARDHAN. S .SHASTRI"
wrote in message
...

A column is having some data and i want to check how many data is repeated
in column B .
I will briefly try to explain it as follows.

A B
a a
b c
c e
d h
e i
f o
g b
h x

Here i am expecting answer as 5 as a,b,c,e and h are common in both
columns.

H S Shastri

Hi,

You can also try this array formula (Ctrl+Shift+Enter)

=COUNT((1/COUNTIF(E5:E12,D5:D12)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"HARSHAWARDHAN. S .SHASTRI"
wrote in message
...

A column is having some data and i want to check how many data is repeated
in column B .
I will briefly try to explain it as follows.

A B
a a
b c
c e
d h
e i
f o
g b
h x

Here i am expecting answer as 5 as a,b,c,e and h are common in both
columns.

H S Shastri