Yes…you can count unique vales using a formula.

This is often comes handy when you are looking for unique sales rep names in your commission file.

Or maybe while reviewing sales file with large number of products.

Count unique vales using a formula:

For this tutorial I’m going to use Excel COUNTIFS formula hence it’s important to understand this formula first.

How does COUNTIFS work:

COUNTIFS is a derivative of COUNT/COUNTA function.

The basic purpose of COUNT/COUNTA formula is to count a number of items in a list irrespective of whether they are duplicates are not.

COUNT/COUNTA formula will not accept any conditions.

Hence COUNTIF formula came in to picture.

With COUNTIF you can apply a condition and count only those.
For e.g. Count only product ‘A’ in a list.

Though COUNTIF somewhat better than COUNT but it’s not enough. For example what if you want to count based on multiple conditions.

Thus COUNTIFS formula has been introduced

With COUNTIFS formula you can apply multiple conditions. For example Count number of transactions related to product ‘A’ and only for ‘WEST’ region.

Ok…enough of theory, lets do a quick example.

Also read: 51 Best Excel Tips & Tricks

COUNTIFS Example:

Here is an example where I have used all three formulas to get desired results.

Count-Countifs-example

Ok..let’s count unique values

Now that we know how to use these formulas I’ll straightaway walk you through our main topic.

I have a report with a list of all James Bond movies by Film, Year, Actor, Director etc… So based on this data I’d like to count the number of unique Directors.

So please follow below simple steps:

Step 1: insert a separate column at the end of our data table and let’s call it as ‘Count Director‘

Step 2: In first row add a simple COUNTIFS formula as below

criteria range1: select Director column, Important Note – do not select the entire column, select only data range i.e. D2: D27 without freezing range

criteria 1: select only first item in the ‘Director’ column

Step 3: Copy paste same formula till the end

Step 4: You will see numbers starting from 1,2,3….n, in which 1 means unique and anything above 1 is a duplicate.

Count unique values

Step 5: Finally you just need to add COUTNIF formula and select this range and give criteria as “1”

Coutn formula

Conclusion:

I just showed one example, but there are many ways you use this formula to count unique values.

Often times it comes handy when you want to count unique customers in your pipeline or unique opportunities.

Do let me know how you use this method in your daily life.