I try to find a formula to compare two range of cells, say A1:E1 (1,2,3,4,5) and A2:E2 (2,3,1,4,5) , and if they have the same values (the numbers can be in different order), it will show the numbers of matches. The cells may have repetitions ( e.g, 1,2,2,2,3). Thanks.

Shortly, if we consider two range cells as two groups, I need a formula to show the number of matches. Numbers may not be in an order, there can be repetitions.


I found this formula but it doesn't do the job in this task. =SUMPRODUCT(COUNTIF(A1:E1,A2:E2))

Example1: {1,1,1,1,1} and {1,1,1,1,1} here the number of matches should be 5. Example2: {1,2,4,5,6} and {1,2,2,4,5} here the number of matches should 4. And above formula doesn't give the correct results.


I assume you can use Row 3 and Column F.

In cell A3 use =A1=A2 this will give you a value of TRUE if A1 matches A2, repeat for B3through E3. Then in Cell F3 use =COUNTIF(A3:E3;"TRUE")

This will give you the number of cells where the value in Row 1 matches that in Row 2

enter image description here

  • Thanks Kevin. In the example you showed, there seems to be 3 matches but there are actually 5 matches. The numbers can be not in order but they should still be a match if the ranges have the same value. and I cant' use Row 3 for this because there will be more data down.
    – user761065
    Oct 18 '17 at 11:51
  • I do not understand how you think there is 5 matches. C1 is not the same as C2, nor is B1 the same as B2. There are only 3 matches, those in A, D and E. Did you mean that B1 is the same as C2? If so this solution will not work (hint, format your question next time, it will help people give you more accurate answers). Oct 19 '17 at 8:52
  • What I meant is that Row1 has (1,2,3,4,5) and Row2 has (1,2,3,4,5), both groups are the same. So there are 5 matches. I mentioned this in my question that numbers can be in different order.
    – user761065
    Oct 19 '17 at 11:43
  • Your question is very unclear. As I said above, please use proper formatting in future so as to remove any confusion. 1, 2, 3, 4, 5 is not proper formatting. Oct 19 '17 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.