How to Use the Anchored COUNTIF Formula in Excel

The Anchored COUNTIF is one of my favorite Excel formula tricks.   Using this technique provides a number of unique benefits, which make the formula worthwhile to learn.  While not as common as some of the more popular lookup formulas out there, being able to write an Anchored COUNTIF is still a great tool that can actually be used in conjunction with lookup formulas to enhance your overall proficiency with database theory.

Click here to download an Excel workbook example using the Anchored COUNTIF Formula

What an Anchored COUNTIF Does

An Anchored COUNTIF is typically used with a table of transactional data.  To use it properly, you’d create a new field for this formula and copy the formula all the way down your table.  When you finish writing an Anchored COUNTIF, it creates:

A unique, ascending count representing all occurrences of an entity within a field that you have referenced

Below is an example of what an Anchored COUNTIF would look like:

In this particular example, we have a table of transactional sales data, showing both the “Product” that was sold and “Sales Price” the product was sold for.  Because this table is sorted by product, you can easily see how the Anchored COUNTIF is working.  Each individual product gets a unique, ascending count, and once a new product appears, the count starts over.

Below is another example of the same table and formula, this time with the values sorted by “Sales Price.”  It’s harder to tell with this view, but the Anchored COUNTIF is still producing the same result, providing a unique, ascending count of appearances for each product in the table.

The example above actually demonstrates one of the key features of the Anchored COUNTIF:  it still works even if you re-sort  your table.  (The formula does not get jumbled)

It’s important to note that, while the Anchored COUNTIF still fulfills its primary objective (producing a unique, ascending count) the count values associated with each of your entries may change after you re-sort.  For example, the entry associated with “3” for the Longhorn product may become “4” after you re-sort your table.

When to Use an Anchored COUNTIF

Seeing an Anchored COUNTIF by itself is just the first step to understanding its value.  After you’ve written the formula, there are four primary ways to utilize the Anchored COUNTIF output:

• Identifying the max occurrences of any entity
• Identifying the first occurrence of any entity
• Identifying the maximum or minimum value of an entity (requires a proper sort)
• Providing a lookup key for the first, maximum, or minimum value of an entity

We’ll cover how to utilize each of these in detail, but first, let’s review how to write the initial formula.

Click here to download an Excel workbook example using the Anchored COUNTIF Formula

How to Write the Anchored COUNTIF Formula

Writing an Anchored COUNTIF is a fairly simple process.  To begin, you’ll need to know which field you want to use it on.

Let’s use the same table of transactional data we referenced before.  The Anchored COUNTIF will be more useful in situations with transactional data (where related lines of data will possibly repeat) rather than summary data (where data is generally summarized and only unique entities are shown).

The table we are using shows a sales rep’s sales data for various products.  To recap from before, each row represents an individual transaction, and for each we have the amount of sale (“Sales Price“) as well as the product type (“Product“) that was sold.

In this particular situation, we want to use the Anchored COUNTIF on “Product” field because that is the one field where we have repeating values.

The COUNTIF Syntax

The Anchored COUNTIF as a whole is really just a single COUNTIF formula – it is not a combination formula.  The syntax for the COUNTIF is as follows:

= COUNTIF ( range , criteria )

Range – the range of cells that the COUNTIF formula will be searching within

Criteria – the specific value the COUNTIF formula is looking for within the range

We’ll use the COUNTIF formula as the foundation of this process.

 

Step 1:  Start Writing the COUNTIF Formula

Step 2:  Select the Field you want to have a unique, ascending count of

Step 3:  Hit the colon “:” key to create a range

Notice when you hit the colon key, the same cell you referenced pops up to the right of the colon.  What you’ve done is created a range reference in Excel that starts and ends with the cell you selected.  For the very first cell, this doesn’t seem very important, but will make more sense once we drag the formula down.

Step 4:  Input a comma to close out the Range input

Step 5:  Select the same cell you originally referenced as the criteria

Step 6: Close out the formula with a parenthesis

Step 7:  IMPORTANT: Go back into your formula and reference lock the first value of the Range syntax for your COUNTIF

This is a very important step as the anchored COUNTIF will not work without this.  This reference locking step is essentially how you’re “anchoring” your formula.

Step 8:  Copy the formula down to complete the process

The easiest way to do this is to double click on the lower right hand corner of the cell you want to copy down.

How it Works

The key to how an Anchored COUNTIF works is the fact that your reference range is “anchored” to your very first cell.  Notice how when you drag your formula down, the range of the COUNTIF formula expands to include all the entries you were looking at before.

What you are telling Excel to do is, within the range I just referenced, tell me how many times this value has appeared.  This is the logic that produces the unique, ascending count of values.

Let’s take the 4th appearance of Nitro as an example.  I’m asking Excel to tell me how many times Nitro has appeared in the table up to and including this point in the table.  The formula will count itself (because you are referencing the same value as the end of the range) and every previous occurrence of the product Nitro.  Therefore, the resulting Anchored COUNTIF value will be 4.

Click here to download an Excel workbook example using the Anchored COUNTIF Formula

If we go down to the very next row, the criteria changes to Xenon.  Since there are no prior occurrences of Xenon, the formula only counts itself and the resulting value is 1.

How to Use the Anchored COUNTIF Formula

We’ve gone through the steps required to create the Anchored COUNTIF and walked through the formula logic.  Now let’s get into how you can actually use the values you created.

Identifying the max occurrences of any entity

Let’s say I want know the maximum number of any single product that my sales rep has sold.  Figuring this out is very simple after I’ve written my Anchored COUNTIF.  Just write a MAX formula across all of your Anchored COUNTIF values.  This formula will tell you the maximum count that was reached for any product.  In this case, that number is 5.

Identifying the first occurrence of any entity

The first occurrence or transaction within this table is always going to have “1” as the Anchored COUNTIF value.  Therefore, all you need to do to identify these is find all the entries with the number 1 in this column.  I’ll highlight these in green below to identify them visually, but we’ll go through a formulaic method in a later example.

Identifying the maximum or minimum value of an entity (requires a proper sort)

Let’s say I wanted to identify the minimum value for product.  Basically, the sale for each product that was transacted at the lowest sale price.  All you would have to do is perform an ascending sort on your column based on the value in question.  (select the field you want to sort on, and use the shortcut sequence: ALT → A → S → A)  You can do this either before or after you’ve written the Anchored COUNTIF formula.  Because it is an ascending sort, the first appearance of an item is equivalent to the lowest sale price of an item.  Therefore, you can use the same logic from before, referencing each row that with a “1” to determine the lowest sale price.

Providing a lookup key for the first, maximum, or minimum value of an entity

Our prior examples have used a visual reference to identify particular values of interest.  We can take the Anchored COUNTIF further by creating a formulaic reference.  All you have to do is create a lookup key.

Let’s say I wanted to know the top three selling transactions for all products in this table.  First off, I would perform a descending sort on the sales price, so that the appearance order of each line matches the “Sale Price” rank.  Then I would just need to create a simple lookup key that concatenates the Anchored COUNTIF value with the “Product” name.

After this lookup key has been created, I now have a column with a unique value for every sales transaction, ranked by sales amount.  All I need to do now to fulfill the requirement is create a simple table, with products and ranks indicated, and run a lookup formula to determine the top 3 transactions in terms of “Sales Price.”  For more information on how to write lookup formulas, visit this page.

Conclusion

The Anchored COUNTIF is a trick I’ve used many times, but have rarely reflected on.  Its benefit can be extremely valuable when performing data analysis.  To fully harness the power of the formula, it’s imperative that you also learn how to perform lookup functions in Excel, particularly INDEX MATCH and VLOOKUP.  Overall, if you haven’t learned it already, I’d definitely recommend adding the Anchored COUNTIF to your Excel toolkit.

Click here to download an Excel workbook example using the Anchored COUNTIF Formula