1. ## countif

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. 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. just Column A.

thanks

4. 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. 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. 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.....

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
•