Formula returns wrong count

[CARBOB]

The formula in G2 returns the wrong count. I thought it had worked in other workbooks, the correct value should be 5.

Excel Workbook
	A	B	C	D	E	F	G
3	03/05/13	16	24	33	35	36	2
4	03/04/13	14	18	20	30	35
5	03/03/13	6	13	14	24	27
6	03/02/13	5	18	23	24	28
7	03/01/13	13	16	23	25	27
8	02/28/13	5	6	17	24	30
9	02/27/13	4	12	14	25	35
10	02/26/13	9	19	27	29	32
11	02/25/13	5	21	27	34	35
12	02/24/13	3	19	24	32	35
13	02/23/13	13	26	28	31	33
14	02/22/13	9	22	27	29	31
15	02/21/13	3	13	15	28	33
16	02/20/13	2	3	14	24	33
17	02/19/13	10	25	30	35	36
18	02/18/13	13	15	17	20	24
Sheet1

Excel 2007

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[NBVC]

That formula counts the maximum number of times a number in any single row within B4:F18 match the numbers in B3:F3.... which is 2.

if you're looking for how many of the numbers in B3:F3 are matched in the range B4:F18, then use:

=SUMPRODUCT(--(COUNTIF(B4:F18,B3:F3)>0))

 

[CARBOB]

That formula counts the maximum number of times a number in any single row within B4:F18 match the numbers in B3:F3.... which is 2.

if you're looking for how many of the numbers in B3:F3 are matched in the range B4:F18, then use:

=SUMPRODUCT(--(COUNTIF(B4:F18,B3:F3)>0))

thank you very much!!