+ Reply to Thread
Results 1 to 7 of 7

COUNTIF Any of column A match any of column B

  1. #1
    Registered User
    Join Date
    11-29-2010
    Location
    Nashville
    MS-Off Ver
    2010
    Posts
    76

    COUNTIF Any of column A match any of column B

    I know how to do this problem in newb-like long form. I would say "=COUNTIF(A:A,B1)" then "=COUNTIF(A:A,B2)", then sum my whole column to find that X number of values in column B matched values in Column A. But that requires a preparation column C to aggregate the information. I'd like to accomplish the same thing in a single cell with one formula.

    Thanks for your help!

  2. #2
    Forum Contributor darknation144's Avatar
    Join Date
    01-24-2012
    Location
    London
    MS-Off Ver
    Microsoft Excel 365 MSO
    Posts
    555

    Re: COUNTIF Any of column A match any of column B

    my solution didn't work so I deleted it
    Last edited by darknation144; 04-25-2012 at 11:32 AM.
    If someone helped give them rep using the star button.

    If you have received a satisfactory solution please mark the thread solved. If not Fotis will come for you at night :P

  3. #3
    Registered User
    Join Date
    11-29-2010
    Location
    Nashville
    MS-Off Ver
    2010
    Posts
    76

    Re: COUNTIF Any of column A match any of column B

    I'm pretty sure that this is a SUMPRODUCT problem, but I still don't have my mind wrapped around that concept.

    Darknation144, what didn't work?

  4. #4
    Registered User
    Join Date
    11-29-2010
    Location
    Nashville
    MS-Off Ver
    2010
    Posts
    76

    Re: COUNTIF Any of column A match any of column B

    I'm uploading a sample excel spreadsheet showing my expected result with some basic sample data, as well as the long and ineffective way I usually do this.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-29-2010
    Location
    Nashville
    MS-Off Ver
    2010
    Posts
    76

    Re: COUNTIF Any of column A match any of column B

    Just did it with an Array formula {=Count(Match((A1:A10,B1:B10),0))}

    Any ideas w/ SUMPRODUCT instead?

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: COUNTIF Any of column A match any of column B

    Maybe this

    =SUMPRODUCT((COUNTIF(B3:B8,A3:A8)*1))

  7. #7
    Registered User
    Join Date
    11-29-2010
    Location
    Nashville
    MS-Off Ver
    2010
    Posts
    76

    Re: COUNTIF Any of column A match any of column B

    Thanks Cutter, that seems to work

+ 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