|
» Filtering a List into Unique Records Using the COUNTIF Formula
CATEGORY: Excel Filter |
VERSIONS: All Microsoft Excel Versions |
|
To filter a List into unique records using the COUNTIF formula: 1. Select cell B2, and insert the formula =IF(COUNTIF($A$2:A2,A2)1,1,0). 2. Copy the formula from cell B2 downwards. 3. From the Data menu, select Filter, AutoFilter. 4. Select cell B1 and press Alt+Down Arrow to open the filtering dropdown list or open it by clicking the dropdown arrow and then selecting 0. |
| Screenshot // Filtering a List into Unique Records Using the COUNTIF Formula |
  |
|
RATING:
3.07
VIEWS: 73905 |
||||
CountIF would not work
Gizmo wrote on December 31, 1969 19:00 EST
By following the instructions above, the "To filter a List into unique records using the COUNTIF formula:" would not work for me.
filter list using countif
Tarqwin wrote on December 31, 1969 19:00 EST
I had to alter the formula for it to work,
=IF(COUNTIF($A$2:$A$13,A2)=1,1,0)
where $A$2:$A$13 was the list,
I could have renamed it List1 and had this
=IF(COUNTIF(List1,A2)=1,1,0)
and again drag the formula down.
This formula does not work for UNIQUE records
jP_in_Calif wrote on December 31, 1969 19:00 EST
I make the assumption that this ExcelTip is an error on the part of the webmasters post.
Using this formula will only produce a value of '1' (in the cell of the formula) where there is only 1 instance of the element in th list. If there are multiple instances of the element in the list, the returned value is 0.
As stated in the example, the COUNTIF portion of the function will return the 'count' of the number of occurances of 'criteria' in 'list' i.e. A2 in range a2:a12.
The portion of the formula containing 'IF', (the equals 1,1,0) states that, if the result of COUNTIF is =1, then its 1 else it's 0 .
Formula Doesn't work and Screen Capture is Wrong
Christine wrote on December 31, 1969 19:00 EST
I couldn't get this formula to work, even with the changes suggested. Also, the screen capture doesn't appear to relate to this tip at all.
Formula Doesn't work and Screen Capture is Wrong
Tarqwin wrote on December 31, 1969 19:00 EST
That's right, the conditional format is to highlight the rows with the unique occurences in column A with this formula for all the cells in that row and then drag down.
=IF(COUNTIF(list1,$A2)=1,1,0) with formatting to suit
Filter for Unique and Multiple Occurences
Nick wrote on December 31, 1969 19:00 EST
Rather than give you only the first occurrence, this formula will search the entire column and show which data have unique entries, and which have multiple entries:
=IF(COUNTIF($A$2:$A$65536,A2)=1,"UNIQUE","MULTIPLE")
Then use Data-->Filter-->AutoFilter
Change in formula
Thangamani wrote on December 31, 1969 19:00 EST
This formula will be a better option.
=IF(COUNTIF($A:$A,A2)=1,"Unique","Multiple")
Sponsors:
 |
Book Store:
Recommended Books:
- The Fall of Advertising and the Rise of PR
- The South Beach Diet: The Delicious, Doctor-Designed, Foolproof Plan for Fast and Healthy Weight Loss
- Absolute Beginner's Guide to Microsoft Office Excel 2003
- Learn MS Excel 2002 VBA/XML Programming
- Financial Risk Manager Handbook, Second Edition
- The McGraw-Hill Guide to Writing a High-Impact Business Plan: A Proven Blueprint for First-Time Entrepreneurs
Related MS EXCEL TIPS:
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
