Count in excel 2013

Count in excel 2013 DEFAULT

How to count cells by criteria

To count cells by criteria, do the following:

   1.   Select the cell where want Excel to return the number of cell by criteria.

COUNTIF example Excel 2013

   2.   Do one of the following:

  • On the Formula tab, in the Function Library group, select the More Functions button and then select Statistical:
    Formula Excel 2013

    Choose COUNTIF in the list.

  • Click the Insert Function button Insert Function button in the left of the edit bar:
    Edit bar Excel 2013

    In the Insert Function dialog box:

    Insert Function Excel 2013
    • select Statistical in the Or select a category listbox
    • select COUNTIF in the Select a function list

   3.   In the Function Arguments dialog box, full following fields:

Insert Function Excel 2013
  • The Range field determines the range of cells Excel will look to perform the count in. In this example the cell range is D2:D19.
  • The Criteria is a conditional statement that is similar to the conditional statement in the IF statement.

   4.   Press OK.

COUNTIF example Excel 2013

Note: You can enter this formula using keyboard, for this example:

= COUNTIF (D2:D19, ">200")

If you want to use COUNTIF on a selection of cells (not necessarily one solid range), summing multiple COUNTIFs:

= SUM (COUNTIF (D3, ">100"), COUNTIF (D7, ">100"), COUNTIF (D14, ">100"), COUNTIF (D17, ">100"))

Note:

  • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
  • Microsoft Excel provides additional functions that can be used to analyze your data based on a condition.
    • To calculate a sum based on a string of text or a number within a range, use the SUMIF worksheet function (see How to sum cells by criteria for more details).
    • To have a formula return one of two values based on a condition, such as a sales bonus based on a specified sales amount, use the IF worksheet function.
    • To count cells that are empty or not empty, use the COUNTA and COUNTBLANK functions.
Sours: https://www.officetooltips.com/excel_2013/tips/how_to_count_cells_by_criteria.html

Count Distinct Values in Excel Pivot Table (Easy Step-by-Step Guide)

Excel Pivot Tables are amazing (I know I mention this every time I write about Pivot Tables, but it’s true).

With a basic understanding and a little drag and drop, you can get a bucket-load of work done in a few seconds.

While a lot can be done with a few clicks in Pivot Tables, there are some things that would need a few extra steps or a little bit of work around.

And one such thing is to count distinct values in a Pivot Table.

In this tutorial, I will show you how to count distinct values as well as Unique Values in an Excel Pivot table.

But before I jump into how to count distinct values, it’s important to understand the difference between ‘distinct count’ and ‘unique count’

Distinct Count Vs Unique Count

While these may seem like the same thing, it’s not.

Below is an example where there is a dataset of names and I have listed unique and distinct names separately.

Difference between unique count and distinct count

Unique values/names are those that only occur once. This means that all the names that repeat and have duplicates are not unique. Unique names are listed in column C in the above dataset

Distinct values/names are those that occur at least once in the dataset. So if a name appears three times, it’s still counted as one distinct name. This can be achieved by removing the duplicate values/names and keeping all the distinct ones. Distinct names are listed in column B in the above data set.

Based on what I have seen, most of the times when people say that they want to get the unique count in a Pivot Table, they actually mean distinct count, which is what I am covering in this tutorial.

Count Distinct Values in Excel Pivot Table

Suppose you have the sales data as shown below:

Pivot Table Data from which distinct values need to be counted

Click here to download the example file and follow along

With the above dataset, let’s say that you want to find the answer to the following questions:

  1. How many sales rep are there in each region (which is nothing but the distinct count of sales reps in each region)?
  2. How many sales rep sold the printer in 2020?

While Pivot Tables can instantly summarize the data with a few clicks, to get the count of distinct values, you will need to take a few more steps.

If you’re using Excel 2013 or versions after that, there is an inbuilt functionality in Pivot Table that quickly gives you the distinct count. And if you’re using Excel 2010 or versions before that, you will have to modify the source data by adding a helper column.

The following two methods are covered in this tutorial:

  • Adding a helper column in the original data set to count unique values (works in all versions).
  • Adding the data to a data model and using Distinct Count option (available in Excel 2013 and versions after that).

There is a third method which Roger shows in this article (which he calls the Pivot the Pivot Table method).

Let’s get started!

Adding a Helper Column in the Dataset

Note: If you’re using Excel 2013 and higher versions, skip this method and move to the next one (as it uses an inbuilt Pivot Table functionality – Distinct Count).

This is an easy way to count distinct values in the Pivot Table as you only need to add a helper column to the source data. Once you have added a helper column, you can then use this new data set to calculate the distinct count.

While this is an easy workaround, there are some drawbacks to this method (covered later in this tutorial).

Let me first show you how to add a helper column and get a distinct count.

Suppose I have the data set as shown below:

Pivot Table Data from which distinct values need to be counted

Add the following formula in Column F and apply it for all the cells that have data in the adjacent columns.

=IF(COUNTIFS($C$2:C2,C2,$B$2:B2,B2)>1,0,1)

The above formula uses the COUNTIFS function to count the number of times a name appears in the given region. Also, note that the criteria range is $C$2:C2 and $B$2:B2. This means that it keeps expanding as you go down the column.

For example, in cell E2, the criteria ranges are $C$2:C2 and $B$2:B2 and in cell E3 these ranges expand to $C$2:C3 and $B$2:B3.

This ensures that the COUNTIFS function counts the first instance of a name as 1, the second instance of the name as 2, and so on.

Since we only want to get the distinct names, the IF function is used which returns 1 when a name appears for a region the first time and returns 0 when it appears again. This makes sure that only distinct names are counted and not the repeats.

Below is how your dataset would look like when you have added the helper column.

Distinct Count Helper Column in Pivot Table Data

Now that we have modified the source data, we can use this to create a Pivot Table and use the helper column to get the distinct count of the sales rep in each region.

Below are the steps to do this:

  1. Select any cell in the dataset.
  2. Click the Insert Tab.Click the Insert Tab
  3. Click on Pivot Table (or use the keyboard shortcut – ALT + N + V)Click on Pivot Table option
  4. In the Create Pivot Table dialog box, make sure that the Table/Range is correct (and includes the helper column) and’New Worksheet’ in selected.Create Pivot Table Dialog box
  5. Click OK.

The above steps would insert a new sheet which has the Pivot Table.

Drag the ‘Region’ field in the Rows area and ‘D Count’ field in the Values area.

Pivot table Areas to get distinct count

You will get a Pivot Table as shown below:

Pivot Table with sum of distinct count

Now you can change the column header from ‘Sum of D count’ to ‘Sales Rep’.

Drawbacks of Using a Helper Column:

While this method is pretty straight forward, I must highlight a few drawbacks that come with modifying the source data in a Pivot Table:

  • The data source with the helper column is not as dynamic as a Pivot Table. While you can slice and dice the data any way you want with a Pivot Table, when you use a helper column, you lose a part of that ability. Let’s say that you add a helper column to get the count of a distinct sales rep in each region. Now, what if you also want to get the distinct count of sales rep selling printers. You will have to go back to the source data and modify the helper column formula (or add a new helper column).
  • Since you’re adding more data to the Pivot Table source (which also gets added to the Pivot Cache), this can lead to a higher size of Excel file.
  • Since we are using an Excel formula, it may make your Excel Workbook slow in case you have thousands of rows of data.

Add Data to Data Model and Summarize Using Distinct Count

Pivot Table added new functionality in Excel 2013 that allows you to get the distinct count while summarizing the data set.

In case you’re using a previous version, you’ll not be able to use this method (as should try adding the helper column as shown in the method above this one).

Suppose you have a dataset as shown below and you want to get the count of the unique sales rep in each region.

Excel Pivot Table Data Set

Below are the steps to get a distinct count value in the Pivot Table:

  1. Select any cell in the dataset.
  2. Click the Insert Tab.Click the Insert Tab
  3. Click on Pivot Table (or use the keyboard shortcut – ALT + N + V)Click on Pivot Table option
  4. In the Create Pivot Table dialog box, make sure that the Table/Range is correct and New Worksheet in Selected.
  5. Check the box which says – “Add this data to the Data Model”Create PivotTable Dialog Box - Check Add Data to Data Model
  6. Click OK.

The above steps would insert a new sheet which has the new Pivot Table.

Drag the Region in the Rows area and Sales Rep in the Values area. You will get a Pivot Table as shown below:

Pivot Table created with data model

The above Pivot Table gives the total count of the Sales rep in each region (and not the distinct count).

To get the distinct count in the Pivot Table, follow the below steps:

  1. Right-click on any cell in the ‘Count of Sales Rep’ column.
  2. Click on Value Field SettingsClick on Value Field Settings
  3. In the Value Field Settings dialog box, select ‘Distinct Count’ as the type of calculation (you may have to scroll down the list to find it).Click on Distinct Count in Value Field Setting Dialog Box
  4. Click OK.

You will notice that the name of the column changes from ‘Count of Sales Rep’ to ‘Distinct Count of Sales Rep’. You can change it to whatever you want.

Distinct Count of Sales Rep

Some things you know when you add your data to the Data Model:

  • If you save your data in the data model and then open in an older version of Excel, it will show you a warning – ‘Some pivot table functions will not be saved’. You may not see the distinct count (and the data model) when opened in an older version that doesn’t support it.
  • When you add your data to a Data Model and make a Pivot Table, it will not show the options to add calculated fields and calculated columns.

Click here to download the example file

What If You Want to Count Unique Values (and not distinct values)?

If you want to count unique values, you don’t have any inbuilt functionality in the Pivot Table and will have to rely on helper columns only.

Remember – Unique values and distinct values are not the same. Click here to know the difference.

One example could be when you have the below data set and you want to find out how many sales rep are unique to each region. This means that they operate in one specific region only and not the others.

In such cases, you need to create one of more than one helper columns.

For this case, the below formula does the trick:

=IF(IF(COUNTIFS($C$2:$C$1001,C2,$B$2:$B$1001,B2)/COUNTIF($C$2:$C$1001,C2)<1,0,1),IF(COUNTIF($C2:C$22,C2)>1,0,1),0)

The above formula checks whether a sales rep name occurs in one region only or in more than one region. It does that by counting the number of occurrence of a name in a region and dividing it by the total number of occurrences of the name. If the value is less than 1, it indicates that the name occurs in two or more than two regions.

In case the name occurs in more than one region, it returns a 0 else it returns a one.

The formula also checks whether the name is repeated in the same region or not. If the name is repeated, only the first instance of the name returns the value 1, and all other instances return 0.

This may seem a bit complex, but it again depends on what you’re trying to achieve.

So, if you want to count unique values in a Pivot Table, use helper columns and if you want to count distinct values, you can use the inbuilt functionality (in Excel 2013 and above) or can use a helper column.

Click here to download the example file

You May Also Like the Following Pivot Table Tutorials:

Sours: https://trumpexcel.com/count-distinct-pivot-table/
  1. Self made training facility owner
  2. Scheels lacrosse
  3. Lunar aquarium lights
  4. Atom minipc
  5. Bialetti pans

Excel COUNTIF Function

The COUNTIF function in Excel counts the number of cells in a range that match one supplied condition. Criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Criteria can also be based on a value from another cell, as explained below.

COUNTIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIF requires a cell range, you can't use an array.

COUNTIF only supports a single condition. If you need to apply multiple criteria, use the COUNTIFS function. If you need to manipulate values in the range argument as part of a logical test, see the SUMPRODUCT and/or FILTER functions.

Basic example

In the worksheet shown above, the following formulas are used in cells G5, G6, and G7:

=COUNTIF(D5:D12,">100")// count sales over 100=COUNTIF(B5:B12,"jim")// count name = "jim"=COUNTIF(C5:C12,"ca")// count state = "ca"

Notice COUNTIF is not case-sensitive, "CA" and "ca" are treated the same.

Double quotes ("") in criteria

In general, text values need to be enclosed in double quotes (""), and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes, as seen in the second example below:

=COUNTIF(A1:A10,100)// count cells equal to 100=COUNTIF(A1:A10,">32")// count cells greater than 32=COUNTIF(A1:A10,"jim")// count cells equal to "jim"

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will return the count of values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.

=COUNTIF(A1:A10,"<"&B1)// count cells less than B1

Not equal to

To construct "not equal to" criteria, use the "<>" operator surrounded by double quotes (""). For example, the formula below will count cells not equal to "red" in the range A1:A10:

=COUNTIF(A1:A10,"<>red")// not "red"

Blank cells

COUNTIF can count cells that are blank or not blank. The formulas below count blank and not blank cells in the range A1:A10:

=COUNTIF(A1:A10,"<>")// not blank=COUNTIF(A1:A10,"")// blank

Dates

The easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this:

=COUNTIF(A1:A10,">"&B1)// count dates greater than A1

Notice we must concatenate an operator to the date in B1. To use more advanced date criteria (i.e. all dates in a given month, or all dates between two dates) you'll want to switch to the COUNTIFS function, which can handle multiple criteria.

The safest way to hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2020, you can use a formula like this

=COUNTIF(A1:A10,"<"&DATE(2020,4,1))// dates less than 1-Apr-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=COUNTIF(A1:A5,"*apple*")// cells that contain "apple"

To count cells in A1:A5 that contain any 3 text characters, you can use:

=COUNTIF(A1:A5,"???")// cells that contain any 3 characters

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

OR logic

The COUNTIF function is designed to apply just one condition. However, to count cells that contain "this OR that", you can use an array constant and the SUM function like this:

=SUM(COUNTIF(range,{"red","blue"}))// red or blue

The formula above will count cells in range that contain "red" or "blue". Essentially, COUNTIF returns two counts in an array (one for "red" and one for "blue") and the SUM function returns the sum. For more information, see this example.

Notes

  • COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.
  • COUNTIF only supports one condition. Use the COUNTIFS function for multiple criteria.
  • Text strings in criteria must be enclosed in double quotes (""), i.e. "apple", ">32", "ja*"
  • Cell references in criteria are not enclosed in quotes, i.e. "<"&A1
  • The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).
  • To match a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).
  • COUNTIF requires a range, you can't substitute an array.
  • COUNTIF returns incorrect results when used to match strings longer than 255 characters.
  • COUNTIF will return a #VALUE error when referencing another workbook that is closed.
Sours: https://exceljet.net/excel-functions/excel-countif-function
How to use the COUNTIF function in Excel

Last updated: April 16, 2019

Excel 2013 has a lot of formulas that can help you to analyze your data. Often these formulas allow you to perform mathematical operations on your cells (like subtraction), and formulas make it easy to update those operations if you happen to change the data in one of those cells.

Occasionally, however, you might need to use Excel to do something that would be inconvenient to do manually, such as count the number of letters, numbers, or special characters that are contained within a cell. Fortunately Excel 2013 has a formula that can automate this character count, thereby saving you the hassle of needing to manually count all of those characters.

Excel Character Counting Formula

If you’re just visiting this page to get the formula quickly, then here it is:

=LEN(XX)

For additional information on using this formula, as well as pictures for how it should be used, you can continue to the next section.

Excel 2013 – How to Count the Characters in a Cell

The steps below are going to show you how to use a formula to count the number of characters in one of the cells of your spreadsheet. Like other formulas in Excel, if you copy the cell with the formula and paste it into the rest of the cells in a row or columns, Excel will automatically update the cell references to count the characters in other cells, too.

You could even take this a step further and use a SUM formula underneath a column where you’ve counted the characters in individual cells to get a character count for that entire column.

Step 1: Open the Excel spreadsheet containing the cell with characters that you want to count.

Step 2: Click inside the cell where you want to display the amount of characters in your target cell.

select the cell where you want to display the character count

Step 3: Type =LEN(XX) but replace the XX part of the formula with the cell location that you want to count. In the picture below, for example, the formula would be =LEN(A2).

how to count the number of characters in a cell in Excel 2013

Step 4: Press the Enter key on your keyboard to execute the formula. You should then see the count of characters in the target cell.

completed LEN formula in Excel 2013

Additional Notes

  • Excel will count spaces as characters with this formula.
  • You can get a total character count for multiple cells by adding the SUM function. For example, a formula that counted the total number of characters in cells A2 through A6 would look like =SUM(LEN(A2),LEN(A3),LEN(A4), LEN(A5), LEN(A6))

Summary – How to count characters in cells in Excel

  1. Click inside the cell where you want to display the character count.
  2. Type =LEN(XX) and replace XX with the cell that you want to count.
  3. Press Enter on your keyboard.

Do you have a sequence of cells that all contain a space or special character, and you are looking for a way to remove those? Learn how to remove the first character from a cell with a formula and save yourself some time in editing your data.

Matthew Burleigh

Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.

After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.

His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.

Related

solveyourtech.com newsletter

Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.

Categories ProgramsTags excel 2013Sours: https://www.solveyourtech.com/count-number-characters-cell-excel-2013/

Excel 2013 in count

COUNTIF function

Use COUNTIF, one of the statistical functions, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.

In its simplest form, COUNTIF says:

  • =COUNTIF(Where do you want to look?, What do you want to look for?)

For example:

  • =COUNTIF(A2:A5,"London")

  • =COUNTIF(A2:A5,A4)

Your browser does not support video.

COUNTIF(range, criteria)

Argument name

Description

range    (required)

The group of cells you want to count. Range can contain numbers, arrays, a named range, or references that contain numbers. Blank and text values are ignored.

Learn how to select ranges in a worksheet.

criteria    (required)

A number, expression, cell reference, or text string that determines which cells will be counted.

For example, you can use a number like 32, a comparison like ">32", a cell like B4, or a word like "apples".

COUNTIF uses only a single criteria. Use COUNTIFS if you want to use multiple criteria.

Examples

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Data

Data

apples

32

oranges

54

peaches

75

apples

86

Formula

Description

=COUNTIF(A2:A5,"apples")

Counts the number of cells with apples in cells A2 through A5. The result is 2.

=COUNTIF(A2:A5,A4)

Counts the number of cells with peaches (the value in A4) in cells A2 through A5. The result is 1.

=COUNTIF(A2:A5,A2)+COUNTIF(A2:A5,A3)

Counts the number of apples (the value in A2), and oranges (the value in A3) in cells A2 through A5. The result is 3. This formula uses COUNTIF twice to specify multiple criteria, one criteria per expression. You could also use the COUNTIFS function.

=COUNTIF(B2:B5,">55")

Counts the number of cells with a value greater than 55 in cells B2 through B5. The result is 2.

=COUNTIF(B2:B5,"<>"&B4)

Counts the number of cells with a value not equal to 75 in cells B2 through B5. The ampersand (&) merges the comparison operator for not equal to (<>) and the value in B4 to read =COUNTIF(B2:B5,"<>75"). The result is 3.

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")

Counts the number of cells with a value greater than (>) or equal to (=) 32 and less than (<) or equal to (=) 85 in cells B2 through B5. The result is 3.

=COUNTIF(A2:A5,"*")

Counts the number of cells containing any text in cells A2 through A5. The asterisk (*) is used as the wildcard character to match any character. The result is 4.

=COUNTIF(A2:A5,"?????es")

Counts the number of cells that have exactly 7 characters, and end with the letters "es" in cells A2 through A5. The question mark (?) is used as the wildcard character to match individual characters. The result is 2.

Common Problems

Problem

What went wrong

Wrong value returned for long strings.

The COUNTIF function returns incorrect results when you use it to match strings longer than 255 characters.

To match strings longer than 255 characters, use the CONCATENATE function or the concatenate operator &. For example, =COUNTIF(A2:A5,"long string"&"another long string").

No value returned when you expect a value.

Be sure to enclose the criteria argument in quotes.

A COUNTIF formula receives a #VALUE! error when referring to another worksheet.

This error occurs when the formula that contains the function refers to cells or a range in a closed workbook and the cells are calculated. For this feature to work, the other workbook must be open.

Best practices

Do this

Why

Be aware that COUNTIF ignores upper and lower case in text strings.

Criteria aren't case sensitive. In other words, the string "apples" and the string "APPLES" will match the same cells.

Use wildcard characters.

Wildcard characters —the question mark (?) and asterisk (*)—can be used in criteria. A question mark matches any single character. An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) in front of the character.

For example, =COUNTIF(A2:A5,"apple?") will count all instances of "apple" with a last letter that could vary.

Make sure your data doesn't contain erroneous characters.

When counting text values, make sure the data doesn't contain leading spaces, trailing spaces, inconsistent use of straight and curly quotation marks, or nonprinting characters. In these cases, COUNTIF might return an unexpected value.

Try using the CLEAN function or the TRIM function.

For convenience, use named ranges

COUNTIF supports named ranges in a formula (such as =COUNTIF(fruit,">=32")-COUNTIF(fruit,">85"). The named range can be in the current worksheet, another worksheet in the same workbook, or from a different workbook. To reference from another workbook, that second workbook also must be open.

Note: The COUNTIF function will not count cells based on cell background or font color. However, Excel supports User-Defined Functions (UDFs) using the Microsoft Visual Basic for Applications (VBA) operations on cells based on background or font color. Here is an example of how you can Count the number of cells with specific cell color by using VBA.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See also

To count cells that aren't blank, use the COUNTA function

To count cells using multiple criteria, use the COUNTIFS function

See a video on how to use the COUNTIFS function

The SUMIF function adds only the values that meet a single criteria

The SUMIFS function adds only the values that meet multiple criteria

IFS function (Microsoft 365, Excel 2016 and later)

Use the TRIM function to remove leading and trailing spaces from cells

Use the CLEAN function to remove non-printing characters from cells

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Statistical functions

Excel functions (alphabetical)

Excel functions (by Category)

Using SUMIF, COUNTIF, and related functions for quick data analysis (free preview)

Sours: https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34
Excel Magic Trick 1053: Unique Count With Criteria: Excel 2013 PivotTable Distinct Count Function

Count Unique Items in Pivot Table

Contextures

How to count unique items (count distinct) in an Excel pivot table. Three methods, for different versions of Excel. Get the free workbook to follow along

Thanks to Roger Govier, who created this tutorial and workbook.

Unique Count

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

  • How many unique (distinct) 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 unique count of items in a pivot table.

  • In Excel 2013 and later, create a Data Model
  • In Excel 2010, and later versions, use a technique that "Pivot the Pivot table".
  • Or, in older versions, add a new column to the source data, and Use CountIf.
  • 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 Data Model.

Introduction

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.

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, follow these 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 OK

add to the data model

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

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

  • Right-click one of the values in the Person field
  • Click Value Field Settings

select Value Field Settings

  • In the Summarize Value Field By list, scroll to the bottom, and click Distinct Count, then click OK

select Distinct Count

The Person field changes, and instead of showing the total count of transactions, it shows a distinct count of salespeople's names.

Distinct Count of customer names

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.

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

3. Use COUNTIF -- Excel 2007 and earlier

In earlier versions of Excel, pivot tables don't have the option of repeating row labels. 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 sample file at the link 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.

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.

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

Get the Sample Files

  • Data Model: Download the Data Model sample workbook, which has the example for Method 1 (Data Model). The file is zipped, and in xlsx format. The file does not contain macros.
  • No Data Model: Download the non-Data Model sample workbook, which contains examples for Method 2 (pivot a pivot) and Method 3 (column in source data). The file is zipped, and in xlsb format. The file does not contain macros.
  • Power Pivot Video: To follow along with the Power Pivot video in Method 4, download the sample file that was used for the video

Related Tutorials

Naming Ranges

Count Unique in PowerPivot

Sample Files - Roger Govier

About the Developer

Roger Govier is an Excel MVP based in the UK who undertakes assignments in Excel and VBA for clients worldwide. While he enjoys the intellectual challenge of solving problems with worksheet functions, Roger claims to be intrinsically lazy, so he always looks for a fast and simple way to provide solid workable solutions.

Find more of Roger's tutorials and sample files here: Sample Files - Roger Govier

You can contact Roger at: [email protected]

Roger Govier Roger Govier

Technology 4 U

Last updated: July 5, 2021 8:29 PM

Sours: https://www.contextures.com/pivottablecountunique.html

Similar news:

The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5.

Your browser does not support video.

Syntax

COUNT(value1, [value2], ...)

The COUNT function syntax has the following arguments:

  • value1    Required. The first item, cell reference, or range within which you want to count numbers.

  • value2, ...    Optional. Up to 255 additional items, cell references, or ranges within which you want to count numbers.

Note: The arguments can contain or refer to a variety of different types of data, but only numbers are counted.

Remarks

  • Arguments that are numbers, dates, or a text representation of numbers (for example, a number enclosed in quotation marks, such as "1") are counted.

  • Logical values and text representations of numbers that you type directly into the list of arguments are counted.

  • Arguments that are error values or text that cannot be translated into numbers are not counted.

  • If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are not counted.

  • If you want to count logical values, text, or error values, use the COUNTA function.

  • If you want to count only numbers that meet certain criteria, use the COUNTIF function or the COUNTIFS function.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Data

12/8/08

19

22.24

TRUE

#DIV/0!

Formula

Description

Result

=COUNT(A2:A7)

Counts the number of cells that contain numbers in cells A2 through A7.

3

=COUNT(A5:A7)

Counts the number of cells that contain numbers in cells A5 through A7.

2

=COUNT(A2:A7,2)

Counts the number of cells that contain numbers in cells A2 through A7, and the value 2

4

COUNTIF function (counts the number of cells that meet a criterion)

COUNTA function (counts the number of cells that are not empty in a range)

Sours: https://support.microsoft.com/en-us/office/count-function-a59cd7fc-b623-4d93-87a4-d23bf411294c


469 470 471 472 473