Want to count all repeated number only once in Excel 2007.

[sierra spiegel]

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/tutorial...97-f0235cdcb480/spambot-killer-aspnet-ma.aspx

 

[Per Jessen]

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
news:[email protected]...

 

[T. Valko]

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))