Excel tip: Use COUNTIF to manage duplicate list values
David H Ringstrom continues his exploration of Excel functions with an explanation of how COUNTIF can work with LARGE and SMALL functions to manage numerical lists.
My last tutorial demonstrated how the LARGE and SMALL functions can help you find the largest or smallest values in an Excel list. This follow-on piece shows how COUNTIF can help you deal with items that share the same value.
Last time around, LARGE and SMALL helped us identify the extreme values, and MATCH and INDEX functions were applied to return the item names that corresponded with those values. But if two items on a list share the same value, MATCH/INDEX will return the same name for both.
Instead, using the following formula determines how many times the value appears in the list and subtracts 1 from it:
If the value appears on the list only once, there's no reason to change the original value. If the amount appears more than once, the formula will add .0001 to it based on the number of times that it has appeared previously in the list. As shown here, kiwi sales in cell C6 become 637.0001. If strawberry sales were also 637, its amount would become 637.0002.
Instead of banans occupying third and fourth place, COUNTIF has return both types of fruit with equal sales. I hope the trick will help you deal with similar “ties” you encounter in your Excel spreadsheets.
"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and contributes articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. You can find further ExcelZone tutorials from David H Ringstrom here.