+ Reply to Thread
Results 1 to 6 of 6

Count how many rows contain a number of matches with another

  1. #1
    Registered User
    Join Date
    02-17-2017
    Location
    Brazil
    MS-Off Ver
    2010
    Posts
    6

    Count how many rows contain a number of matches with another

    I have this range C1:Q2000, each row contains 15 numeric values, and AA1:AT1 with 20 numeric values.
    I need to count how many rows in C1:Q2000 contain 15 matches with AA1:AT1

    This is the formula I use to count matches
    =SUMPRODUCT(COUNTIF(C1:Q1, AA1:AT1))
    But would only count one row at a time and I don't seem to come up with an idea of how to sum the counts of all the 2000 rows and only if there are 15 matching values.

    All I know is I could make a column with the count of each row, and count how many times the result was 15, that would do it, but I have to repeat this from AA1:AT1 until AA3000:AT3000 and not only look for 15 matches but also 0,1,2... matches up to 15. It gets complicated... I'm looking if there is a simpler way.

    I have found some sheets that do this with a smaller amount of numbers, but all use vba. Is there a way of doing this only using formulas?

    Thanks for your help!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: Count how many rows contain a number of matches with another

    Hi, welcome to the forum

    1. Please update your profile as necessary to properly reflect the general area (country) you come from. Members may need to tailor answers based on your regional settings - and "earth" just does not do it

    2. Please upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

    hanks
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-17-2017
    Location
    Brazil
    MS-Off Ver
    2010
    Posts
    6

    Re: Count how many rows contain a number of matches with another

    Hi, thanks.

    I didn't know that was relevant as I don't have a fixed location in the world :P
    but if it's necessary alright.
    If that's for the language, I must tell I'm using excel in english and I only know the functions in english.

    I'm uploading a reduced version with only a few rows so you can have an idea of what I'm working with, in this sample I just need to know how to count how many rows at C1:Q3 have 15 matching numbers with AA2:AT2 so I can do the rest.
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Count how many rows contain a number of matches with another

    Try this formula in BJ2 and fill down.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit Since I do not know what the regional (if any) array separators are in the version you are using let me know if this is not working.
    Last edited by FlameRetired; 02-17-2017 at 08:00 PM.
    Dave

  5. #5
    Registered User
    Join Date
    02-17-2017
    Location
    Brazil
    MS-Off Ver
    2010
    Posts
    6

    Re: Count how many rows contain a number of matches with another

    This worked perfectly! Thank you

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Count how many rows contain a number of matches with another

    Your are welcome. Glad it does the job. Thank you for the feedback and rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 07-14-2016, 11:54 AM
  2. [SOLVED] Need to count the number of unique values after 1st part of cell matches
    By PeteABC123 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-11-2014, 03:26 PM
  3. [SOLVED] Count number of occurrences but if there are consecutive matches count them as one
    By michaljireht in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-26-2013, 08:26 PM
  4. Replies: 5
    Last Post: 01-05-2013, 01:28 AM
  5. [SOLVED] count how many times one number matches to a data field
    By Chubster in forum Excel General
    Replies: 2
    Last Post: 10-16-2012, 08:20 AM
  6. Count if number matches critera
    By lazyme in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-29-2007, 04:52 PM
  7. How do I count number of cels the matches 2 conditions ?
    By Abra in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-27-2005, 05:06 PM

Tags for this Thread

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