COUNT COMMON DATA
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 |
COUNT COMMON DATA
=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 |
COUNT COMMON DATA
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 |
COUNT COMMON DATA
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 |
All times are GMT +1. The time now is 11:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com