none
How to find/count duplicate cells in worksheet

    Question

  • I have an Excel 2010 worksheet with two columns that contain about 500 cells each.  The contents of the cells are names (such as "Bob Smith").  I need to get a count of all names that appear in both columns.  I've tried this:

    =COUNTIF($A$2:$A$500, B$2:B$500)

    no luck.

    What I'm looking for is a simple number to appear in the cell that contains this formula.

    Kinda new with Excel formulas... appreciate the help.

    Thursday, December 12, 2013 5:01 PM

Answers

  • As an array formula, confirmed with Ctrl+Shift+Enter:

    =SUM(COUNTIF(A2:A500,B2:B500))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by john1519 Thursday, December 12, 2013 5:22 PM
    Thursday, December 12, 2013 5:11 PM

All replies

  • Array enter - enter using Ctrl-Shift-Enter

    =SUM(IF(NOT(ISERROR(MATCH($A$2:$A$500, B$2:B$500,FALSE))),1))

    This assumes that there are no repeated values within column A or B.

    Thursday, December 12, 2013 5:10 PM
  • As an array formula, confirmed with Ctrl+Shift+Enter:

    =SUM(COUNTIF(A2:A500,B2:B500))


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by john1519 Thursday, December 12, 2013 5:22 PM
    Thursday, December 12, 2013 5:11 PM
  • appears to work like a champ.  thanks!
    Thursday, December 12, 2013 5:23 PM