Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Excel 2010 - Count common elements

Dear Experts,

Can you please have a look at the attached file, it contains small data on Sheet1 and Sheet2, both in A column.

Could you advise which formula could give back a number that how many common elements are on the two sheets in column A? In the example this would be 3, because only the 101,102 and 107 are on both sheets

thanks,
CommonInColumn.xlsx
0
csehz
Asked:
csehz
  • 3
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

try

=SUM(--(NOT(ISERROR(MATCH(A2:A7,Sheet1!A2:A5,0)))))

This is an Array formula and needs to be entered with Ctrl-Shift-Enter

Or a non-array formula, confirmed with a simple Enter

=SUMPRODUCT(--(NOT(ISERROR(MATCH(A2:A7,Sheet1!A2:A5,0)))))

cheers, teylyn
0
 
csehzIT consultantAuthor Commented:
Teylyn thanks very much it works, can I put such array formula also to VBA?

thanks,
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Possibly. But that would be a different question, since this one has been answered.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
csehzIT consultantAuthor Commented:
Thanks :-)
0
 
barry houdiniCommented:
Can there be duplicates? For example if A6 became 102 there would still arguably be 3 common elements......but teylyn's formulas would return 4?

regards, barry
0
 
csehzIT consultantAuthor Commented:
Barry well for me fortunately on the live files the columns sure do not have duplicates. So I even did not think to such scenario, however yes can be a good next question too :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now