Formula returns wrong count

Thanks:  0
Likes:  0

# Thread: Formula returns wrong count

1. ## Formula returns wrong count

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

Sheet1
ABCDEFG
303/05/1316243335362
403/04/131418203035
503/03/13613142427
603/02/13518232428
703/01/131316232527
802/28/1356172430
902/27/13412142535
1002/26/13919272932
1102/25/13521273435
1202/24/13319243235
1302/23/131326283133
1402/22/13922272931
1502/21/13313152833
1602/20/1323142433
1702/19/131025303536
1802/18/131315172024
Excel 2007

Array Formulas
CellFormula
G3=MAX(MMULT(IF(ISNUMBER(MATCH(\$B4:\$F18,\$B3:\$F3,0)),1,0),TRANSPOSE(COLUMN(\$B\$12:\$F18)^0)))
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

2. ## Re: Formula returns wrong count

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

3. ## Re: Formula returns wrong count

Originally Posted by 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))

thank you very much!!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•