[Webinar] Streamline your web hosting managementRegister Today

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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

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