Results 1 to 7 of 7

Thread: countif

  1. #1

    countif



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi everyone,

    I need help with countif to find duplicates in the first columns of multiple sheets.
    the sheets have names April15/April30/May15 etc.I use excel 2010.

    I'm a rookie with formulas so please explain why it works this way.


    Thanks

  2. #2
    Are you looking across the sheets to find duplicates in the same cell, or are you looking at each sheet seperately and looking for duplictes in Column A?

    Greg

  3. #3
    just Column A.

    thanks

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Try this.

    Go to the first sheet, select column A... then name it (by typing into the Name Box above A1 and left of the Formula bar)... call it ColA1

    Go to the next sheet, and repeat calling that column A, ColA2

    repeat similarly for each sheet. The key is to use consecutively numbered naming.. (don't skip any numbers).

    Then go back to sheet 1, go to Home|Conditional Formatting|New Rule.

    Select Use a formula to determine which cells to format.

    Enter this formula:

    =SUMPRODUCT(COUNTIF(INDIRECT("ColA"&ROW(INDIRECT("1:3"))),A1))>1

    Then click Format and choose the preferred colour.

    Repeat for each of the sheets.

    Only the duplicated values should be highlighted.


  5. #5
    NVBC,thank you for your help.I used it but the duplicates value didn't show up.I think I do something wrong or didn't understand how to do it.

    Here is the example,the columns are longer than I put here .











    sheet April 15 sheet April30
    1366039379 1366388567
    1366037895 1366377757
    1366037040 1366293217
    1366027626 1366296217
    1366033810 1366303080
    1366040480 1366295504
    1366039792 1366295366
    1366029820 1366215442
    1365686184 1366136053
    1365684199 1366217796
    1365601266 1366143920
    1365608543 1366143791
    1365599256 1366208735
    1365602192 1366121798
    1365450263 1366051915
    1365528988 1366131912
    1365446815 1366122864
    1365511960 1366108854
    1365510504 1366118188
    1365511820 1366039379
    1365515688 1366118562
    1365427804 1366115070
    1365428711 1365513167
    1365185297 1366398067
    1365173962 1366734356
    1365175223 1366379378
    1365164103 1366397654
    1365107226

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    hHeris a sample. Please review it. Notice that it doesn't matter the sheetnames. I named column A of Sheet1, ColA1, and column A of Sheet2 is ColA2.The formula for conditional formatting is then: =SUMPRODUCT(COUNTIF(INDIRECT("ColA"&ROW(INDIRECT("1: 2 "))),A1))>1 Notice that the "1:3" is now "1:2" tbecause you have only 2 sheets in this sample.....
    Attached Files Attached Files


  7. #7
    I really appreciate it.

    all the best

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •