Results 1 to 3 of 3

Formula returns wrong count

This is a discussion on Formula returns wrong count within the Excel Questions forums, part of the Question Forums category; The formula in G2 returns the wrong count. I thought it had worked in other workbooks, the correct value should ...

  1. #1
    Board Regular
    Join Date
    Jun 2005
    Posts
    1,530

    Default 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. #2
    Board Regular NBVC's Avatar
    Join Date
    Aug 2005
    Location
    Ontario
    Posts
    5,827

    Default 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))
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Microsoft MVP - Excel



  3. #3
    Board Regular
    Join Date
    Jun 2005
    Posts
    1,530

    Default Re: Formula returns wrong count

    Quote Originally Posted by NBVC View Post
    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!!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com