+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Finding the maximum number of matches in a matrix of data

  1. #1
    Registered User
    Join Date
    12-04-2008
    Location
    chicago
    Posts
    11

    Finding the maximum number of matches in a matrix of data

    I have a screen shot of what I am trying to accomplish

    Capture1.JPG

    In Column F I want to sum up the maximum number of matches from Col B:E
    irregardless if they are -1 or 1's

    I am using Excel 2007

  2. #2
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Finding the maximum number of matches in a matrix of data

    Would this work for you?

    =MAX(COUNTIF(A1:D1,1),COUNTIF(A1:D1,-1))
    Sincerely
    S?ren Larsen

    "Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Finding the maximum number of matches in a matrix of data

    Try this formula in F3 copied down

    =MAX(COUNTIF(B3:E3,{1,-1}))

    If you want it to work with any values, rather than just 1 and -1 then try this version

    =MAX(COUNTIF(B3:E3,B3:E3))

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1