Utilizing Excel’s COUNTIF Function to Break Ties

In a recent article I demonstrated how you can use the LARGE and SMALL functions to isolate the x largest or smallest values within a list. I then used the MATCH and INDEX functions to return the corresponding names associated with the values. However, if two items on a list share the same value, MATCH/INDEX will return the same name for both items, as shown in Figure 1. In today's article, I'll describe how to use the COUNTIF function to create a tiebreaker in such situations.

Figure 1: Ties within a ranking list can result in duplicate matches on the item name.

In Figure 1, I used the LARGE function to rank items from largest to smallest. The LARGE function in cell G2 takes this form:

=LARGE(B$2:B$11,E2)

This formula is then copied down through cell G11.

In cell F2, I used this formula to return the corresponding names.

=INDEX(A$2:A$11,MATCH(G2,B$2:B$11,0))

As shown in Figure 1, bananas appear on the list twice in column F. This is because kiwi sales are also 637. As shown above, the MATCH function in cell F4 is looking for the number 637, based on cell G4. It finds this amount in cell B5, and so it quits looking and returns 5 as the row amount that INDEX should use to return 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 solution to this problem is to make the figures in cells B2 through B11 be unique, without materially affecting the amounts. To do so, we can use the COUNTIF function, which has two arguments:

In this case, I'm going to use the COUNTIF function to add a multiple of .001 to values that appear multiple times on the list. To do so, I initially put this formula in cell C2:

=COUNTIF($B$2:B2,B2)

I then copied this down through cell C11. As you can see in Figure 2, this counts the number of times that each value appears on the list. Notice my judicious use of the $ signs to indicate absolute references. I want to create an expanding range, so I anchor the starting point at cell B2 by 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.

Figure 2: The COUNTIF function can determine the number of times a number appears within a list.

Now that I've identified the duplicates, the next step is to add a tiny increment to the duplicate amounts to make each be unique. I modified the previous formula to take this form:

=(COUNTIF($B$2:B2,B2)-1)*0.0001+B2

In this case, I'm letting COUNTIF determine how many times the value has appeared on the list and subtracting 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, I want to add .0001 to it based on the number of times that it's appeared on the list. As shown in Figure 3, kiwi sales in cell C6 become 637.0001. If strawberry sales were also 637, its amount would become 637.0002.

Figure 3: This revised version of COUNTIF adds a tiny, additional amount to duplicate values.

Once I had the formulas in place, I copied cells C2 through C11 to the clipboard, and then right-clicked on cell B2 and chose Paste Special, and then double-clicked on Values, as shown in Figure 4. 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.

Figure 4: Use the Paste Special Values option to replace the original values.

In any case, as shown in Figure 5, bananas no longer appear on the list twice. This use of COUNTIF gives you an effective means to break ties when necessary within your Excel spreadsheets.

Figure 5: The duplicate wording in our ranking list has been removed.

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com  or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link