COUNT COMMON DATA

[HARSHAWARDHAN. S .SHASTRI]

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

 

[Gary''s Student]

=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.

 

[Roger Govier]

Hi

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

 

[Ashish Mathur]

Hi,

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

=COUNT((1/COUNTIF(E5:E12,D512)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"HARSHAWARDHAN. S .SHASTRI"