I have a data:

set A
3 21 23 30 43
1 20 36 52 53
18 19 36 45 59
9 16 27 35 57

set B
4 16 23 36 53

I want to create a formula that will count how many number in set B are repeated in set A. So set B has 16, 23, 36, 53 repeated in set A. there return value should be 4 because I only one to count each repeat number in set B only once. I have count the =countif(a1:e4,a6)+countif(a1:e4,a7)....but it gave me the total sum of each repeats..5. And that's not what I wanted? Please help.

thanks

EggHeadCafe - Software Developer Portal of Choice
Spambot Killer ASP.NET Mailto: Hyperlink Control
http://www.eggheadcafe.com/tutorials...aspnet-ma.aspx

Hi

Look at this:

=IF(COUNTIF($A$1:$E$4,A6)0,1,0)+IF(COUNTIF($A$1:$ E$4,A7)0,1,0).......

Regards,
Per

"sierra spiegel" skrev i meddelelsen
...
I have a data:

set A
3 21 23 30 43
1 20 36 52 53
18 19 36 45 59
9 16 27 35 57

set B
4 16 23 36 53

I want to create a formula that will count how many number in set B are
repeated in set A. So set B has 16, 23, 36, 53 repeated in set A. there
return value should be 4 because I only one to count each repeat number in
set B only once. I have count the
=countif(a1:e4,a6)+countif(a1:e4,a7)....but it gave me the total sum of
each repeats..5. And that's not what I wanted? Please help.

thanks

EggHeadCafe - Software Developer Portal of Choice
Spambot Killer ASP.NET Mailto: Hyperlink Control
http://www.eggheadcafe.com/tutorials...aspnet-ma.aspx

Try this...

Set A in the range A1:E4
Set B in the range A10:E10

=SUMPRODUCT(--(MMULT(--(ISNUMBER(MATCH(A1:E4,A10:E10,0))),{1;1;1;1;1})0) )

--
Biff
Microsoft Excel MVP


<sierra spiegel wrote in message
...
I have a data:

set A
3 21 23 30 43
1 20 36 52 53
18 19 36 45 59
9 16 27 35 57

set B
4 16 23 36 53

I want to create a formula that will count how many number in set B are
repeated in set A. So set B has 16, 23, 36, 53 repeated in set A. there
return value should be 4 because I only one to count each repeat number in
set B only once. I have count the
=countif(a1:e4,a6)+countif(a1:e4,a7)....but it gave me the total sum of
each repeats..5. And that's not what I wanted? Please help.

thanks

EggHeadCafe - Software Developer Portal of Choice
Spambot Killer ASP.NET Mailto: Hyperlink Control
http://www.eggheadcafe.com/tutorials...aspnet-ma.aspx