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:
So, how can you prevent this happening and retain the integrity of your data? Read on to find out how COUNTIF can operate as a “tiebreaker” in such situations.
In the figure above, the rankings in column G were achieved by applying the following LARGE function argument, starting from cell G2, and filled down to G11:
In cell F2, this INDEX formula returned the corresponding names:
This rankings illustrated above display bananas twice in column F. This is because kiwis had an identical number of sales, 637. The MATCH function in cell F4 searches for the number 637, based on the formula in cell G4.
The formul encountered a match in cell B5, so stopped looking and returned 5 as the row amount for INDEX to retrieve the item name. The LARGE function also returns 637 in cell B6, but the MATCH function within cell F5 also returns 5, because MATCH stops looking at a list once it finds a match.
The way around this limitation is to find a way to make the figures in cells B2 through B11 unique, without materially affecting the amounts.
The COUNTIF function can help us do this, by applying two arguments:
- range - This is a range of cells in which we wish to look for a specified value.
- criteria - This represents the value that we're seeking.
COUNTIF can be used to add a multiple of .001 each time it identifies the same value in a list, using this formula:
The formula has been pasted into cell C2 in the example below and then filled down to cell C11. As we can see, the formula returns the number of times that each value appears in the list. Note how absolute references have been applied by putting $ signs in front of the cell address elements. In order to create an expanding range, the starting point can be anchored in cell B2 using “$B$2”. Don't use any dollars signs around the second B2, because we want this to become B3, B4, B5, and so on as we copy the formula down the column.
Now the dupes have been identified, the next step is to add a tiny increment to the duplicate amounts to make each be unique. Do this by modifying the previous formula as follows:
In this case, COUNTIF 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:
Now that we have cooked up the incrementing COUNTIF formula, it’s time to go back and clean up the original fruit sales ranking by de-duping the extra banana reference.
Having filled the COUNTIF formula down column C, copy cells C2 through C11 to the clipboard. Then then right-click cell B2 and chose Paste Special, and double-click Values, as shown below. (If you're using Excel 2010 or later, you can click the Paste Values icon. Within the Paste Special dialog box, double-clicking on Values eliminates the need to click the OK button. You can use this double-click trick in most dialog boxes when you're making a single selection).
Once you've pasted the data, press Escape to clear with Windows clipboard.
And here is the resulting chart. 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.