Главная » Error » Excel Pivot Tables Count Unique Items

Excel Pivot Tables Count Unique Items

В этой статье

Video: How to Get Unique Count

If you’re using a pivot table to summarize your data, you might have
questions such as:

  • How many unique (distinct/different) customers made purchases in each region?
  • How many unique products were sold in each store?
  • How many unique salepeople sold each product per region?

There isn’t a built-in “Unique Count” feature in a normal pivot table, but in this
tutorial, you’ll see how to get a distinct count of items in a pivot
table.

  1. In Excel 2013 and later, create a Data Model in the Excel workbook
  2. In Excel 2010, and later versions, use a technique to “Pivot the Pivot table”.
  3. Or, in older versions, add a new column to the source data, and Use CountIf function
  4. If the Power Pivot add-in is installed, use
    it to show a distinct count

This video shows the steps to show a distinct count with the Excel Data Model.

Return numerical count of unique values

It may be that you are not interested in replicating the actual values from the original list but simply want to know how many of those values there are. Once you understand the UNIQUE function, it is quite simple to append it to the COUNTA function to get this answer.

Since COUNTA counts the number of cells in a range that are not empty, it doesn’t get any easier than combining it with the desired setting in the UNIQUE function.

To count the number of different cities listed from cells B2 to B18 below, we would enter:

=COUNTA(UNIQUE(B2:B18))

Count unique values Excel - numerical count

The result returned is that a total of 10 different cities are represented by the 17 accounts shown.

To determine the number of cities which have only one account holder, the third argument, exactly_once, is entered as TRUE.

=COUNTA(UNIQUE(B2:B18,,TRUE))

Count unique values Excel - numerical count

This principle also applies to the COUNT function to count unique numeric values.

Count Only Unique Text Values from a List

Now, let’s say you have a list of names in which you also have mobile numbers and you want to count unique values just from text values.

So, in this case, you can use the below formula:

=SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))

And when you enter this formula as an array.

{=SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}

How it Works

In this method, you have used the IF function and ISTEXT. ISTEXT first verifies whether all the values are text or not and return TRUE if a value is a text.

After that, IF applies COUNTIF on all the text values where you have TRUE and other values remain blank.

And in the end, SUM returns the sum of all the unique values which are text and you get the count of unique text values this way.

Join Our Newsletters!

First NameEmailGet Excel Tips and Tricks!I’d rather not get FREE tips 🙁

What is a Unique Count?

Often we have large data sets that we want to analyse with the aid
of a Pivot Table, but we only want to count the unique occurrences
of some items of data. Unfortunately, Pivot Tables have never had
the inbuilt facility to count unique values.

In this example, the sample file has 4999 records that show product
sales, with the region and salesperson name. The first few records
are shown in the screen shot below. You can download the sample file
at the link below.

source data for pivot

Creating a Pivot Table from the data would give us something like
the following, if we added Person to the Values area of the Pivot
table and summarised it with Count.

pivot table with person count

However, this is giving us a count of all the transactions, not the
count of the unique number of people who made those transactions within
each Region.

Ways to Get a Unique Count

There are workarounds that you can use, to get a unique count:

  • Method 1: In Excel 2013 and later,
    add the pivot table’s source data to the Data Model, and a unique count can be done easily.
  • Method 2: In Excel 2010 and later,
    use the “pivot a pivot” technique.
  • Method 3: In older versions of Excel, add a column
    to the source data to place a 1 in cells on a row where the value
    is unique, and a 0 in any other cells. Then, summing this additional
    column provides the unique value.
  • Method 4: Use PowerPivot to create the pivot table, and use its functions
    to create a unique count. See
    the details below.

All four methods are described below, along with a performance comparison between methods 2 and 3.

1. Add to Data Model – Excel 2013 and Later

In Excel 2013, if you add a pivot table’s source data to the workbook’s Data Model, it is easy to create a unique count.

NOTE: This technique creates an OLAP-based pivot table, which has some limitations, such as no grouping, and no calculated fields or calculated items. If you need these restricted features, try the “pivot a pivot” method instead.

add to the data model

Create a Unique Count (Distinct Count)

To create the pivot table, try the following steps:

  • Select a cell in the source data table.
  • At the bottom of the Create PivotTable dialog box, add a check mark to “Add this data to the Data Model”
  • Click the OK button

add to the data model

Add Fields to Pivot Table

Next, to set up the pivot table layout, follow these steps:

  • In the pivot table, add Region to the Row area.
  • Add these 3 fields to the Values area — Person, Units, Value
  • The Person field contains text, so it defaults to Count of Person.
    • The count shows the total number of transactions in each region, not a unique count of people

add to the data model

Get the Distinct Count

Finally, to get a unique count of people in each region, follow these steps:

  • Right-click one of the values in the Person field
  • In the pop-up menu, click on Value Field Settings

select Value Field Settings

The Value Field Settings window opens, where:

  • the first tab, Summarize Values By, is showing
  • there is a list of calculation types
  • Count is selected, because the field is currently using that type of calculation

To change the calculation type:

  • In the Summarize Value Field By list, scroll to the bottom of the list
  • Click on the Distinct Count calculation type
  • Click the OK button, to close the dialog box.

select Distinct Count

In the pivot table, the Person field changes automatically.

Now, instead of showing the total count of transactions, it shows a distinct count of salespeople’s names, for each region.

Distinct Count of customer names

Method 5. Array formula using FREQUENCY

The last of our five methods is also the most complex one. That’s because it involves up to five different formulas combined to one long formula combination. But this is the only entirely dynamic solution for counting the number of unique records with multiple criteria.

Structure of the array formula

Count the number of unique records using an array formula.
Count the number of unique records using an array formula.
Count the number of unique records using an array formula.

The structure of the base formula combination is shown in the figure on the right-hand side. You only have to insert two different parts.

  1. The “COUNT RANGE” is the list or range you want to get the number of different values from. In the example of this chapter it’s A2:A11.
  2. The “FIRST CELL OF COUNT RANGE” is basically the first part of the “COUNT RANGE”. Because the count range in your example is A2:A11, it’s just A2.

Applying this formula on the previous example leads to the following formula.

=SUM((FREQUENCY(MATCH(A2:A11,A2:A11,0),ROW(A2:A11)-ROW(A2)+1)>0)*1)Example formula counting the number of unique records using the FREQUENCY formula.
Example formula counting the number of unique records using the FREQUENCY formula.
Example formula counting the number of unique records using the FREQUENCY formula.

Explanation of the FREQUENCY array formula to count the number of unique records

What does this formula do in the background? The basic formula is FREQUENCY (please refer to this article for more information).

  • The array version of the MATCH formula returns the first position of each record within the list of records. If a record appears twice, the second time it’s first position is repeated. That way, the “DATA ARRAY” of text is converted into an array of number.
    The formula of the example at this step: =SUM((FREQUENCY({1,2,3,4,4,6,7,8,9,9},ROW(A2:A11)-ROW(A2)+1)>0)*1)
  • The second argument of the FREQUENCY formula creates an array of numbers, starting with 1 counting up until there is one value for each record. This array is used as the “BIN ARRAY” of the FREQUENCY formula.
    The formula of the example after this step: =SUM((FREQUENCY({1,2,3,4,4,6,7,8,9,9},{1,2,3,4,5,6,7,8,9,10}>0)*1)
  • The FREQUENCY formula returns an array of numbers. These number say for each record, saying how many times it occurs. If a record appears the first time, it’s being counted to the respective bin of how many occurences there are in total. The second and any following occurrence of the same item (already converted to a number by the MATCH formula) is counted into the bin “0”.
    The formula of the example after this step: =SUM(({1,1,1,2,0,1,1,1,2,0,0}>0)*1)
  • The “>0” checks for each number of the FREQUENCY formula, if it’s larger than 0. All first occurences get TRUE and all second, third and more often occurences get a FALSE.
    The formula of the example after this step: =SUM(({TRUE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE})*1)
  • Multiplying this array of TRUEs and FALSEs by 1 converts them into the numerical values of 0 and 1.
    =SUM({1,1,1,1,0,1,1,1,1,0,0})
  • The enlosing SUM formula adds up all 1 which stand for the first occurrence of an item.
    =8

Adding the first criteria

After solving the first question of the example, it’s time to add one criteria. Because the formula as shown before can’t handle blank cells, the first condition will be to skip blank cells.

Structure of the formula for counting unique records under one condition.
Structure of the formula for counting unique records under one condition.
Structure of the formula for counting unique records under one condition.

The image on the right-hand side shows the structure of the FREQUENCY formula with one condition. As you can see, the formula hasn’t changed much. Just one part is added, illustrated with number 3. You insert the condition using the IF formula. The corresponding closing blanket of the IF formula is after the MATCH formula. If the condition is not met, this part of the formula returns FALSE. This results in also FALSE at this point of the “DATA ARRAY” of the FREQUENCY formula.

Add the condition itself the following way: (CONDITION_RANGE=CONDITION)

The condition of our example table is to skip empty cells. The CONDITION_RANGE is A2:A11 and the condition is <>””. Putting the condition into the complete formula leads to the following formula.

=SUM((FREQUENCY(IF(B2:B11<>“”,MATCH(A2:A11,A2:A11,0)),ROW(A2:A11)-ROW(A2)+1)>0)*1)

Adding the second criteria

After knowing how to handle one condition, it’s time to proceed with the second question of our example: How many people had a result higher than 40 in the first trial?

The same way the first condition is added, a second can be inserted. Just add one more IF formula before the existing IF formula. The corresponding closing bracket needs to be entered after the MATCH formula.

In the example of this chapter, the trial number is located in cells B2 to B11 and should be the first trial. The second criteria range is the result, located in cell range C2 to C11 and should be higher than 40. Regarding these two criteria leads to the following formula.

=SUM((FREQUENCY(IF(C2:C11>40,IF(B2:B11=”1st trial”,MATCH(A2:A11,A2:A11,0))),ROW(A2:A11)-ROW(A2)+1)>0)*1)

Once again, the hint: All formulas shown in this method are array formulas. After pressing Ctrl + Shift + Enter on the keyboard, curled brackets are added so that the formula―when seeing in the formula bar―look like this.

{=SUM((FREQUENCY(IF(C2:C11>40,IF(B2:B11=”1st trial”,MATCH(A2:A11,A2:A11,0))),ROW(A2:A11)-ROW(A2)+1)>0)*1)}

2. Pivot the Pivot – Excel 2010

To get a unique count of salespeople per region in Excel 2010, we can
produce a Pivot Report, and then “Pivot the Pivot” to get our final
result. Thanks to Roger Govier, who created this
tutorial and workbook.

Create the First Pivot Table

  • Create a Pivot Table from this data, with Region and Person in
    the Rows area
  • Add Units and Value in the Values area. Because Person is a text
    field, the Pivot table will automatically show it as “Count
    of”.
  • Format the pivot table with the Tabular report layout
  • Set all the Item labels to repeat in each row.
  • Change the Captions, to remove the “Sum of”
    TIP: Type the original field name, with a space character at the
    end, e.g. “units “

pivot table

This now looks like a normal table of data, but it is a summary
of the original full data table with 4,999 rows.

Create a Named Range for the Start Cell

Next, you’ll name the first cell in the pivot table.

  • Select cell B2, which is the heading in the pivot table’s Region
    column
  • Click in the Formula Bar, and type a name for the cell — pvtStart
  • Press Enter, to complete the name

Create a Named Range for the End Row

The next step is to calculate the location of the last row in the
pivot table. We’ll use the MATCH function, with a very large number,
to find the last number in column D, and get its row number. This
formula will only work in a column with numbers.

  • On the Data sheet, in cell M2, enter this formula, to calculate
    the last row in the pivot table’s Units column:

    =MATCH(9.9E+307,Method_1!D:D,1)

  • To name that cell, select cell M2, and in the Name Box, type PvtEnd,
    and press Enter.

Create a Dynamic Named Range

Next, we will create a dynamic Named Range, to refer to this new
table. This range will start in the cell named pvtstart (the
Region heading cell), and end in the last row of data in column E
(the named range, PvtEnd).

  • On the Ribbon’s Formulas tab, click Define Name
  • In the New Name dialog box, enter the name, myData
  • In the Scope drop down, select Workbook
  • In the Refers To box, type this formula:
    =pvtStart:INDEX(Method_1!$E:$E, PvtEnd)
  • Click OK, to create the named range

pivot table

Create the Second Pivot Table

Finally, create a second Pivot Table, based upon the dynamic range,
myData.

Put Region in the Row area, and Person, Units and Value in the Values
area

This shows the number of unique salespeople per regions as shown below.
For example, in the first pivot table, the East region showed 8 unique
names in the person column, and that is the count in this pivot table.

pivot table

Refresh the Pivot Tables

Naturally, as there are 2 Pivot Tables involved in this solution,
both have to be refreshed after any data has been added or changed
in the source table. It is essential that the first PT is refreshed
first, followed by a Refresh of the Final Pivot Table.

You can refresh the pivot tables manually, or use amacro. To use
a macro, add one of the following procedures to the sheet with your
final Pivot Table.

A) If both pivot tables are on the same sheet, use this code:

Private Sub Worksheet_Activate()

Me.PivotTables(1).PivotCache.Refresh

Me.PivotTables(2).PivotCache.Refresh

End Sub

B) If the pivot tables are on different sheets then use this code
(substitute the name of your actual sheet for “yoursheetname”):

Private Sub Worksheet_Activate()

Sheets(“yoursheetname”).PivotTables(1).PivotCache.Refresh
Me.PivotTables(1).PivotCache.Refresh
End Sub

(Optional) Name the Pivot Table Data Range

If you are using macros to refresh the pivot tables, you could also
use a macro to reset the myData named range after each refresh. Add
the following procedure to your workbook, then call this SetmyData
procedure in the Worksheet_Activate procedures.

Sub SetmyData()
‘Change “PT_A” to whatever name
‘you have given to your first Pivot Table
‘that is to be used as the source
‘for for the final Pivot Table

‘Also change the sheet name
‘from “Method_1” to
‘your sheet name if it is different

Dim PTName As String
Dim ShName As String

PTName = “PT_A”
ShName = “Method_1″

ThisWorkbook.Names.Add _
Name:=”myData”, _
RefersTo:=Sheets(ShName) _
.PivotTables(PTName) _
.TableRange1
End Sub

Sample Workbook

What we would like to achieve

To extract unique list of values from the “Fruit” list

How to Get Unique Values Without Unique function? - Example
Example

Download the workbook to practice it by yourself!

Press the download button!

3. Use COUNTIF — Excel 2007 and earlier

In earlier versions of Excel, pivot tables don’t have the option
of repeating row labels.

In those versions, to get a unique count of salespeople per region,
you can add a new column in the source data, and enter a COUNTIF formula

You can download the “No Data Model” sample file in the download section,
below.

Add the COUNTIF Formula

Follow these steps, to add the formula:

In the source data, add a new column heading — “Unique”
— in cell J1

In cell J2, enter this formula, and copy it down to the last row
of data:

=IF(COUNTIF($E$5:E5,E5)>1,0,1)

The first reference in the range ($E$5) is Absolute, and the second
is relative (E5), so as we copy the formula down the page, so the
range will expand from $E$5:E5 to $E$5:E6, then $E$5:E7 and so on.

In each row we are checking how many times the value in column E
(Person) has occurred, down to the current row.

  • If it is greater than
    once, the result is zero, so that person is not counted again.
  • This
    lets us see the number of unique times within the dataset that each
    Person’s name has appeared.

With the extra column added, the data would look like this. The second
instance of “Harry” returns a zero in the Unique column.

source data with COUNTIF

NOTE: If there are people of the same name selling in different Regions:

  • In Excel 2007 or later: Use COUNTIFS, to check multiple criteria. For example, =IF(COUNTIFS($D$5:$D5,D5,$E$5:E5,E5)=1,1,0)
  • In Excel 2003 or earlier, concatenate the Region and
    Person names in a new column, and then use the COUNTIF formula to find Unique values in that concatenated column.

Create a Pivot Table

To see the unique counts in a pivot table:

  • Create a Pivot Table from this data, with Region in the Rows
    area
  • Add Unique, Units and Value in the Values area.
  • Change the “Unique” heading to “Count of Person”
    or “Person “

Pivot Table with Unique field

We can see that there are 30 people who made the Sales, and the number
of each who are in each Region, and if we chose to expand any Region
then we would see the individual totals for each Person.

5. Using Pivot Table to count text values

Easies way to count the value is by creating the pivot table and using the Count of items

1. Create a pivot table

2. Use Count of Items

3. Output Table

Excel formulas not working: Why& How to fix them #

According to the Washingtonpost article on excel errors, “The Australian researchers found that roughly 1 in 5 of these papers included errors in their gene lists that were due to Excel automatically converting gene names to things like calendar dates or random numbers.”

The original research was conducted by Journal Genome Biology found that there is an increasing trend year on year in terms of the number of errors.

You can download complete research materials from the SourceForge website (Open Source) for further reading.

excel errors research paper

The above research paper may not be directly linked to formulas not working in excel, but there is a point here:

Very well built & tested software like MS Excel can go wrong in terms of understanding our data what about you & me?

So, here are our top 10 reasons for formulas not working.

How to fix this issue: #

Head over to Formulas → Calculation → Click on Calculate now/Calculate Sheet option.

You can alternatively use the F9 shortcut key to refresh the workbook.

Here is our video guide on excel calculation options.

Some other tips:

  • Make a habit of pressing Ctl+S [it will force calculation]
  • Do not use this option for every workbook, unless if you have a huge file with lot of formulas
  • This option is a workbook specific thus you need to turn it on or off in every workbook that you want to use

How to fix this issue: #

It’s very simple:

Head over to Formulas → Under formula Auditing → Click on “Show Formulas” (With the first click formulas will show up and the Second click they will hide behind cells and start working)

[Please refer to above screenshot]

Also read: 51 best excel tips and tricks

#3. EXTRA SPACE(S) IS AN EXTRA HEADACHE#

In case you have been extracting data from ERP systems in CSV (comma separated values) format, then your data is mostly exposed to unwanted spaces.

Do you know extra spaces cause a lot of problems while using the VLOOKUP formula?

Sometimes it’s quite hard to identify these spaces.

For example, let’s say if you want to do Vlookup using employee name as a base, so your employee name looks like below

Blank spaces in excel is one of the reason for Vlookup not working

Now if I take a length of each name I can see the first line has 12 and the second one has 13.

Although both the names look similar to our naked eye, there is a blank space in the second name.

This would not meet the exact match criteria in the VLOOKUP formula thus resulting in a #N/A error.

How to fix this issue: #

The extra space(s) issue can be fixed using the Trim formula.
In general, the Trim formula will find out more than one space between words and it will delete.

Some other tips:

  • It’s always a good idea to use the Trim formula before you do Vlookup in both the tables (lookup& data table)
  • The trim function will ignore blank spaces between letters (it will only work with words)

#4. GET RID OF NONPRINTABLE/HIDDEN CHARACTERS#

Similar to leading spaces, nonprintable characters are also one of the reasons for excel formulas not working.

In case, if you have data with these characters, it’s better to take them off before working further.

How to fix this issue: #

Similar to trim, you can use the CLEAN formula to delete these characters.

Use clean formula to remove Nonprintable or Hidden characters-min

Tip: It’s a good idea to use both Trim& Clean formulas together

#5. EXCEL FORMATTING – DON’T TRY TO COMPARE APPLES WITH ORANGES#

It’s very important to have correct formatting before using formulas like Vlookup, Hlookup, and Match& Index.

Numbers formatted as text is one of the common reasons for formulas not working in excel.

Although they look like numbers for our nacked eyes, in reality, they are not.

Number formatting issues in excel

For example, in the above image, you can see Customer ID looks like a number.

But, if you refer above formatting section, you could see that they have been formatted as text.

This, in turn, will trigger the #N/A error while adding the VLOOKUP formula.

The worst part is, it’s a bit difficult to identify.

Here is another example where the numbers are formatted as text with a single quote. This is very common if you extract data from Database.

Numbers with single quote

Again, in this case, your VLOOKUP formula will not work.

How to fix this issue: #

Make sure all your numbers are formatted as numbers instead of text. It’s worth to re-format the lookup column before using the Vlookup formula.

Some other tips:

  • In general, numbers will always be aligned to the right side of the cell (but in our above example they are not)
  • Sometimes you can see a little green tip on the top left corner, that indicates something wrong with that cell (refer second image above)
  • Just click on a number cell –> Click on dropdown from error symbol –> Click on ‘Convert to Number’

#6. CIRCULAR REFERENCES (ERRORS DUE TO AN ENDLESS CIRCLE)#

In simple terms, your formula range is in an endless loop or it’s in a circle.

As per excel calculation rules, your formulas should not reference result cells as a range. If you do so, circular reference errors would show up.

Personally, I’ve struggled many times with these types of errors.

For example, in our below Expense Budget table, we need to add some plug (adjustment number) to match with the targets.

So, we have calculated the differential amount in row 13, and now we just need to put them in row 9.

One simple way is by linking row 13 cells in row 9.

If you do so, your link formula will not work instead, you can see zero, and possibly you could also see Blue coloured arrows pointing to cell references.

[Please refer below]

Circular reference errors example

In general, it should work.

But technically, it will be an error because our formula cell references are in a loop.

How to fix this issue: #

First, we need to identify error cells.

Go to Formulas tab → click on Error Checking drop-down arrow → place cursor on Cell references → now you will see error range(s)

How to identify circular reference error ranges

Second, now you know the circular references range, go to each error cell and fix the formulas.

#7. USING DOUBLE QUOTES INCORRECTLY – HUMAN ERROR#

We often use double quotes while writing formulas.

But, it’s very important to understand when to use and when to not.

For example, to join two numbers we can use a formula like below.

=1234&5678 = 12345678

Alternatively, to join two text strings you can’t do the same instead, you need to specify text within double-quotes.

=John&Smith: Wrong-way of using [you will most likely see #NAME error]

=”John “&”Smith” = This is the correct way.

correct way of using quotes in excel formulas

The same goes with conditional formulas like IF, AND, OR. It’s very important to specify text within double-quotes.

Otherwise, this is one of the reasons for excel formulas not working.

#8. BODMAS – BASIC RULE FOR EVERY CALCULATION#

Do you know every calculation in excel is based on the ‘BODMAS’ rule?

Oh, hang on.. what is BODMAS rule?

It’s a mathematical rule to determine an order of operations.

settingbox -BODMAS rule

Ok, let’s simplify further.

Can you guess what is the answer for the below?

10+10*2 =?

Is that 40, congratulations you are wrong.

Can you try the same calculation in excel?

You will most likely see 30, indeed that’s the correct answer… but how?

According to the BODMAS rule, the calculation will take place based on a sequence i.e → B- Brackets, O- Order, D- Division, M- Multiplication, A- Adition& S- subtraction.

So, the sequence of the above formula is → 10*2 = 20 → 20+10 =30.

Your formulas can go wrong if you don’t consider BODMAS.

How to fix this issue: #

It’s very simple, you just need to change the sequence by using parentheses like below.

=(30+40)*2

So, anything in brackets will be calculated first and then it will go to the next operation.

#9. INCORRECT USE OF “ABSOLUTE” REFERENCING#

Everyone who uses Excel mostly knows what is absolute referencing.

If not, please stop right here and learn about it. Here is a quick tutorial on cell references.

With the help of absolute referencing, you can fix your formula range so that you can copy it from one cell to other.

That’s indeed a very useful feature. That being said, if you don’t know how to use them then your formulas will not work as they intend to be.

Certainly, this is one of the main reasons for excel formulas not working when copied from one cell to another cell.

absolute referencing errors

For example, in our above table, we have used absolute cell referencing to make it easy for us to copy formulas.

But, we did a mistake.

Instead of using partial freeze, we have used the F4 shortcut key and fixed the entire range.

That’s indeed resulted in incorrect results.

So, the correct way should have been using absolute reference for the only rows like this =SUM(D$3:D$6).

How to fix this issue: #

Unfortunately, there are no quick fixes for this type of error. You have to do it manually by analyzing all the formulas.

But, the best way I found so far is by cross-checking formulas& results as soon as I use absolute& relative referencing.

Please let me know your workarounds at this point.

Some other tips:

  • Do not use the F4 shortcut key for a partial freeze
  • Try to include a dollar sign ($) manually for partial range

#10. INCORRECT FORMULA ARGUMENTS#

Incorrect formula arguments can have a negative impact on formula results.

Just to give you some perspective, let’s understand the below vlookup syntax, especially the last one [range lookup].

vlookup formula syntax with detailed explanation

Range lookup argument has two options to select.

  1. True – Approximate match
  2. False – Exact match

Personally, I feel using the “Approximate match” option is like committing suicide.

Because an Approximate match will never return accurate results.

Vlookup issues due to approximate match

In our above example, we have added the Vlookup formula in cell F3 to find out the corresponding revenue amount.

If you observe closely, the Revenue amount of 25k for customer id 6378993 is incorrect. It should have been 65k instead.

So, what went wrong?

As I mentioned before, I’ve used ‘Approximate match’ under the range lookup argument.

The “match” is another formula where you have an option to select similar arguments.

How to fix this issue: #

Never ever use ‘Approximate match’ in your VLOOKUP formulas unless you know what you are doing.

Method 4. Formulas SUMPRODUCT and COUNTIFS

The previous methods 1, 2 and 3 aren’t entirely dynamic. That means, with an update of the data, the results don’t automatically change without further steps. Even the PivotTable in our method 3 requires a refresh. Out methods 4 and 5 don’t have such constraints. They automatically update their results because they are based on Excel formulas.

Add a condition to count unique records

Count number of unique records under one condition using the SUMPRODUCT formula.
Count number of unique records under one condition using the SUMPRODUCT formula.
Count number of unique records under one condition using the SUMPRODUCT formula.

Now, we add a condition. Because this formula regards empty cells as one unique record, the condition might be, that cells mustn’t be empty. The condition can be entered instead of the 1 in the beginning of the SUMPRODUCT formula like in the image on the right-hand side.

If the condition is, that empty cells don’t count, the condition would be COUNT_RANGE<>””. For your example that means

=SUMPRODUCT((A2:A11<>“”)/COUNTIFS(A2:A11,A2:A11&””))

You can of course use different criteria. But again, only one criteria or condition is possible. The second question of your example is “How many people had a result higher than 40 in the first trial?”. This question requires two conditions (higher than 40 and the first trial). But answering the question of how many people had a result higher than 40 (without the condition that it must have happened in the first trial) is possible to answer with this formula. The result is given in cells C2 to C11. That means the criteria is (C2:C11>40). The complete formula is

=SUMPRODUCT((C2:C11>40)/COUNTIFS(A2:A11,A2:A11&””))

Method 2. Using advanced filters

Steps for using
Steps for using
Steps for using “Advanced Filters” function for counting unique values.

The next method—using advanced filters—works very similar to the previous method removing duplicates. Advanced filters in Excel provide a function to filter to unique records only. This advanced filter hides duplicates as you are probably familiar from normal filters. The screenshot on the right-hand side shows the steps.

  1. Start by selecting the list or column containing the values you want to count the unique items from.
  2. Click on “Advanced” within the “Sort & Filter”-group on the “Data”-ribbon.
  3. In the new window check “Filter the list, in-place”, make sure the correct “List range” is selected and set the tick at “Unique reconds only”. Confirm with OK.
  4. The status bar now shows already the number of records left. In this example it says “8 of 10 records found”.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

EmailHP

Topics

  • Formula Basics
  • Formula Examples
  • Dynamic Array Formulas
  • Conditional Formatting
  • Pivot Tables
  • Excel Tables
  • Shortcuts
  • More…

7. Using Amazing Excel Tools

Another simple way to count without duplicates in excel is tools like Kutools by the following steps.

1. Select a blank cell to output the result.

2. Click Kutools>Formula>Helper>Formula Helper.

3. Do the following steps in the formula helper dialog;

  • Check and select Count unique values in the Choose a formula box. You can check the filter box and type some words to filter the formula names.
  • Then choose the range in which you want to count unique values in the range box.
  • Press the OK button.

Show values that only appear once

The third argument of the UNIQUE function is exactly_once, and it’s optional. If TRUE is entered, Excel extracts only values that appear exactly once within the array. 

To get this result in the example below, the formula entered is:

=UNIQUE(A2:A8,,TRUE)

Count unique values Excel - value appears once

In this interpretation of “unique”, values that appear more than once are not returned in the results. Therefore “apple” and “banana” are removed from the results.

Note that in the above example there was no need to enter the second argument (by_col) since a vertical list is the default setting of the UNIQUE function. The second comma was entered as a placeholder.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you’ll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You’ll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas |
101 Functions

Conclusion

Counting unique values can be useful for you while working with large datasets.

Like the name list which you have used here had duplicate names and after calculating unique numbers, we get that there are 10 unique names in the list.

Well, all the methods which you have learned here are useful in different situations and you can use anyone from those which you think is a perfect fit for you.

If you ask me, advanced filter and SUMPRODUCT are my favorite methods, but now you need to tell me:

Which one is your favorite?

Please share your views with me in the comment section, I’d love to hear from you and don’t forget to share this tip with your friends.

Excel Formulas

Explanation

How to Get Unique Values Without Unique function? - Explanation
Explanation

Formula of Cell C5

=IFERROR(INDEX($A$2:$A$9, MATCH(0, COUNTIF($C$1:C4, $A$2:$A$9), 0)), “”)

Let’s dig deep into the array formula of extracting unique values.

To help you better understand the formula, I will explain each part of the formula according to the handling order.

This time we choose Cell C5 as the investigation object since you will be able to understand more with cell C5.

Part 1

COUNTIF($C$1:C1,$A$2:$A$9)

$C$1:C4 refers to “Unique List”, “Apple”, “Pear”, “Plum”

How to Get Unique Values Without Unique function? - $C$1:C4
$C$1:C4

$A$2:$A$9 refers to “Apple”, “Pear”, “Plum”, “Pear”, “Banana”, “Pear”, “Plum”, “Plum”

COUNTIF function count cells that match certain criteria.

Here it counts the number of times $A$2:$A$9 appear in $C$1:C4.

Only “Banana” isn’t in the range $C$1:C4. It’s in Cell C5 instead.

The result is {1, 1, 1, 1, 0, 1, 1, 1}.

Part 2

MATCH(0, COUNTIF($C$1:C4, $A$2:$A$9), 0)

After replacing COUNTIF($C$1:C4, $A$2:$A$9), we get this instead.

MATCH(0, {1, 1, 1, 1, 0, 1, 1, 1}, 0)

MATCH function returns the position of the matched value within an array.

Here the position of “0” in the lookup array is “5” so it will return a “5”.

Part 3

INDEX($A$2:$A$9, MATCH(0, COUNTIF($C$1:C4, $A$2:$A$9), 0))

After replacing above results, we get this instead.

INDEX($A$2:$A$9, 5)

INDEX function returns the nth item in an array.

Here we would like to return the 5th item in the $A$2:$A$9.

The result is “Banana”.

Part 4

IFERROR(INDEX($A$2:$A$9, MATCH(0, COUNTIF($C$1:C4, $A$2:$A$9), 0)), “”)

After replacing above results, we get this instead.

IFERROR(“Banana”, “”)

IFERROR returns a specified value if it detects an error.

Here we set the specified value as blank.

Without the error-handling function, the result would appear as below.

How to Get Unique Values Without Unique function? - Result without error-handling
Result without error-handling

Since we don’t have any error in the formula, it will return the original value, which is “Banana”.

If unique function is not available in Excel, I highly recommend you to try these two formula.

Do you find this article helpful? Subscribe to our newsletter to get exclusive Excel tips!

Share on facebookShare on googleShare on twitterShare on linkedinShare on pinterest
Gladys from Dollar Excel

Gladys from Dollar Excel

All Posts » International Open Academy
How to input time duration in Excel
<хедер class="eael-entry-хедер">

How to Input Time Duration in Excel

Read More How to remove bullets/numberings in Excel
<хедер class="eael-entry-хедер">

How to Remove Bullets/Numberings in Excel

Read More How to generate random numbers with decimals
<хедер class="eael-entry-хедер">

How to Generate Random Numbers with Decimals

Read More How to Stack Multiple Columns into One Column
<хедер class="eael-entry-хедер">

How to Stack Multiple Columns into One Column

Read More How to Delete Row if cell in certain column is Blank? Excel
<хедер class="eael-entry-хедер">

How to Delete Row if cell in certain column is Blank? Excel

Read More How to input time duration in Excel
<хедер class="eael-entry-хедер">

How to Mass Delete hidden rows/ columns in Excel

Read More Load More

Performance Comparison

If possible, use the “pivot a pivot” method, which is much
faster.

COUNTIF Method

Using a COUNTIF formula in the source data works, and gives us our
desired result, and on a relative small dataset the method is acceptable.
But, it is very costly in terms of processing time, and on very large
datasets can be extremely slow.

Using Charles
Williams’ “FastExcel” to calculate the time it takes for the workbook
to calculate gives the result shown below, where the time taken to
recalculate the Data sheet is 224 milliseconds.

recalculation time

Pivot a Pivot Method

The “Pivot a Pivot” method also produced the correct result,
with a unique count of Person, as required. In addition, it does not
rely upon additional columns in the source data, nor entering of any
new formulae.

The resulting workbook is smaller, and again using Charles
Williams’ “FastExcel” to calculate the time for calculation of
the Data sheet is an incredibly fast 1.2 milliseconds

recalculation time

With small datasets timing of recalculation may not be critical,
but when dealing with larger datasets of 300,000 to 400,000 rows,
then these differences would become very material.

Count Unique Values with a UDF

Here I have VBA (UDF) which can help you to count unique values without using any kind of formula.

Function CountUnique(ListRange As Range) As Integer
Dim CellValue As Variant
Dim UniqueValues As New Collection
Application.Volatile
On Error Resume Next
For Each CellValue In ListRange
UniqueValues.Add CellValue, CStr(CellValue) ‘ add the unique item
Next
CountUnique = UniqueValues.Count
End Function

Enter this function in your VBE by inserting a new module and after that go to your worksheet and insert the following formula.

=CountUnique(range)

sample-file.xlsx

Horizontal ranges

If your array is a horizontal range (i.e., displayed across several columns), you should enter TRUE as the second argument in the UNIQUE function. To extract distinct values from the range A2 to G2 below, we use the following formula:

=UNIQUE(A2:G2,TRUE)

Count unique values Excel - horizontal ranges

Entering TRUE for the second argument [by_col] tells Excel that the list is horizontal. This may seem counter-intuitive, but is in fact correct, because the values are found in separate columns (A to G, in this case).

Excel returns a “spilled” list, displaying them in the same format (horizontally) as the original list. The values “apple” and “banana” are duplicated. Hence, they are only returned once in the output list.

If there are not enough empty cells to display all the spilled values, Excel will return a #SPILL! error.

No third argument was entered in the above example because we want a list of all the distinct values that appear in the original list. This is the default setting of the UNIQUE function.

4. Power Pivot

If you have the PowerPivot add-in installed, you can use
it to show a distinct count for a field. This video shows the steps for creating a Power Pivot pivot table, and adding a field with the unique count.

For written instructions, see the Excel 2013 steps on my pivot table blog. There are instructions for Excel 2010 too.

Sample File: To follow along with the video, download the sample file that was used for this video

Источники

  • https://www.contextures.com/pivottablecountunique.html
  • https://www.goskills.com/Excel/Resources/Count-unique-values-Excel
  • https://excelchamps.com/formulas/count-unique-values/
  • https://dollarexcel.com/how-to-get-unique-values-without-unique-function/
  • https://professor-excel.com/number-unique-records/
  • https://www.basicexceltutorial.com/how-to-count-without-duplicates-in-excel/
  • https://www.settingbox.com/excel-formulas-not-working/
  • https://exceljet.net/formula/count-unique-values-in-a-range-with-countif
[свернуть]
Решите Вашу проблему!


×
Adblock
detector