+ Reply to Thread
Results 1 to 3 of 3

Conditional formatting - duplicate entries across 3 columns

  1. #1
    Registered User
    Join Date
    01-05-2016
    Location
    Southampton
    MS-Off Ver
    Excel 2013
    Posts
    40

    Conditional formatting - duplicate entries across 3 columns

    Hi

    I am having trouble working out a formula for finding duplicate data across 3 columns. At the moment I have this formula:

    =countifs(A:A, A1, B:B, B1)>3

    So when data in columns A and B duplicate more than 3 times they highlight red. I need it to include column C also but for some reason it doesn't seem to work. I thought by using this formula "=countifs(A:A, A1, B:B, B1, C:C, C1)>3" it would work but all it does it highlight cells in column A? My range is A1:C1000.

    Thanks for your time!

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,936

    Re: Conditional formatting - duplicate entries across 3 columns

    You need to use absolute addresssing on the columns - otherwise, Excel will update the formula like it had been copied across.

    =countifs($A:$A, $A1, $B:$B, $B1)>3
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    01-05-2016
    Location
    Southampton
    MS-Off Ver
    Excel 2013
    Posts
    40

    Re: Conditional formatting - duplicate entries across 3 columns

    Thanks Bernie! Thought it would be as simple as that. D'oh!

    Cheers

+ 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. [SOLVED] Conditional Formatting Duplicate Value with 2 columns
    By raffi123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-21-2014, 04:12 PM
  2. [SOLVED] match duplicate pair value ONLY between 2 columns using conditional formatting
    By yogi_himalayan in forum Excel General
    Replies: 4
    Last Post: 04-04-2014, 04:54 PM
  3. Conditional Formatting duplicate data in columns
    By DuckMan72 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2013, 05:19 PM
  4. [SOLVED] Help with Conditional Formatting: duplicate entries on different sheets
    By musik7 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2012, 05:40 PM
  5. Replies: 3
    Last Post: 07-20-2010, 11:38 AM
  6. Conditional formatting to highlight duplicate entries
    By legendkiller420 in forum Excel General
    Replies: 1
    Last Post: 07-12-2010, 11:36 AM
  7. Replies: 4
    Last Post: 07-18-2008, 03:11 PM

Tags for this Thread

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