+ Reply to Thread
Results 1 to 4 of 4

formula to determine how many rows have different values in two columns (mismatches)

  1. #1
    Registered User
    Join Date
    10-24-2012
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    25

    formula to determine how many rows have different values in two columns (mismatches)

    Is there any formula that I can put in a different sheet that counts how many rows of data on another sheet have different values (mismatches) on two columns?
    For instance in the below example, the answer is 2:

    25 25
    46 46
    73 28
    56 56
    80 80
    47 36

    Please note that there are also blank rows. I would also like to have the summary of net unmatched provided that on a thrid column there is a particular reference. For example in the above range if all rows have that reference, the answer would be 73-28 + 47-36 = 56

    Thanks in advance,

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: formula to determine how many rows have different values in two columns (mismatches)


    a
    b
    1
    25
    25
    2
    46
    46
    3
    73
    28
    4
    56
    56
    5
    80
    80
    6
    47
    36


    =sumproduct((a1:a6<>b1:b6)*(a1:a6-b1:b6))


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,648

    Re: formula to determine how many rows have different values in two columns (mismatches)

    Assuming your data is in the range A1:B6, to get the unmatched count try this......

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Registered User
    Join Date
    10-24-2012
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: formula to determine how many rows have different values in two columns (mismatches)

    It works! Thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Match two columns of data - return mismatches third column
    By Silge in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-22-2014, 06:53 AM
  2. [SOLVED] How to display mismatches found between two columns?
    By MoRyn in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 03-23-2014, 04:03 PM
  3. [SOLVED] Count Formula for Matches in One Column and Mismatches in Another
    By xcel-noob in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-27-2014, 03:46 PM
  4. Compare cell values in two different columns for mismatches
    By maacmaac in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-11-2012, 01:33 AM
  5. [SOLVED] Could not determine which columns uniquely identify the rows
    By bill in forum Excel General
    Replies: 0
    Last Post: 11-01-2005, 11:05 AM

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