How to Use the Anchored COUNTIF Formula in Excel

by Matthew Kuo on March 26, 2016

in Database Theory, Excel, Formula Writing

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

Anchored COUNTIF 00

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:

Anchored COUNTIF 01

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.

Anchored COUNTIF 02

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.

Anchored COUNTIF 02a

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

Anchored COUNTIF 03

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

Anchored COUNTIF 04

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.

Anchored COUNTIF 05

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

Anchored COUNTIF 06

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

Anchored COUNTIF 07

Step 6: Close out the formula with a parenthesis

Anchored COUNTIF 08

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.

Anchored COUNTIF 09

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.

Anchored COUNTIF 10

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

Anchored COUNTIF 11

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.

Anchored COUNTIF 12

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.

Anchored COUNTIF 13

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.

Anchored COUNTIF 14

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.

Anchored COUNTIF 15

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.

Anchored COUNTIF 16

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.

Anchored COUNTIF 17

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 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

{ 2 comments… read them below or add one }

Sue August 9, 2016 at 8:20 am

Very valuable website.
Really appreciate your work.

Sue

Reply

Roman August 24, 2016 at 8:53 am

This is a very helpful function that already saved me a lot of work in many situations, thank you!

Reply

Leave a Comment

Previous post:

Next post:


Related pages


ucla gpa requirementsadvanced excel formattingmatch command excelrandom username pickerrand excel functionlookup with multiple criteriaexcel histogram graphhlookup and vlookup togetherirs allowances calculatormultiple if functions exceledit array excelexcel 2010 transpose functionhistogram for excelthe goal by eliyahu goldratt summarymba laptopall formulas of excel 2007 with examplehow to create vlookup formulacolumn chartsfuzzy search excelhow to write a formula in excelreverse vlookupsolver excel tutorial step by stepp&l driversmaking charts and graphs in excelvlookup between two workbookstrue or false formula in excelvlookup processduplicate check in excelaccounting formats in excelhow to create a countif formula in excelthe goal a process of ongoing improvement cliff notesucla anderson mbaexcel table lookupvlookup multiple instancesformatting cells in excelexcel format in thousandsadvanced excel tips and techniqueshbr case solutionswebsites with the most trafficname draw randomizerclustered column chart excel 2013excel identify duplicates in a columnexcel hlookup formulaserenity dental culver cityexcel converter to pdfbins excelmba for dummieshow to insert a checkmark into excelirs withholding calcvlookup functions in excelcomplex charts in excelsymbol for check markerror bar excel 2010bin range in excelstacked graphs excelhow to use v look upexcel vba syntax guideexcel hide functionstandard deviation vbacheck mark symbol microsoft worducla accounting programhow do you put a tick in a word documentvlookup multiple worksheetsucla book store hourssample p&l excelexcel example formulasresource calendar template excelexcel duplicates formulanormal plot in excellookup value in excellast name randomizercto ucla ticketshbr business caseharvard business school case studies solutionsexcel filter using formulawww recreation ucla edupowerpoint checkmark
\n