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


vlookup and if statementexcel label chartucla ashe centermba calculatormba colremoving duplicate entries in excel3 axis graph excel 2010ucla statistics coursesexcel value error handlingmicrosoft excel tables tutoriallabeling charts in excelfrequency histogram generatorquick keys for exceleliyahu m goldratt the goaleliminating duplicate rows in excelvlookup multiple rowsusing rand in excelexcel formulas to find duplicatesvlookup in a tablehow to do v lookupsnewest version of excelmicrosoft office excel free trialtick symbol wingdingsexcel equalshow to exclude duplicates in exceltick mark powerpointcopy formatting exceldeleting duplicates in excelexcel generate unique random numbersmatching rows in excelexcel h lookupexcel remove duplicates from listcovert pdf to xlsv lookup for dummiesexcel task tracker templatemicrosoft excel random number generatormultiple vlookup criteriaif statement excel 2010excel delete duplicate rowshow to use a vlookupexcel count duplicates in columnpayment excel templateshort cut for filter in excelhow to use vlookup excel 2010excel formula cheat sheet 2013countif excel exampledeloitte consulting vaultthe goal by eli goldrattvlookup in excel exampleucla mba andersonroulette name pickercomplex vlookupexcel error checkingexcel formulas functionshow do i do a histogram in excelconvert exl to pdfmatching cells in exceldraw histogram in excelexcel 2007 find duplicateserror function in excelvlookup in excel definitioncell count in excelworking at zs associatesexcel formula if a cell containsfuzzy match excelhow to create an histogram in excelshortcut key to insert comment in excelexcel 2010 custom number formatucla anderson marketingvlookup formattingexcel remove gridlinesvba excel columns
\n