Count occurrences of values or unique values in a data ...
[Pages:2]Count occurrences of values or unique values in a data range - Excel -
1 of 2
Count occurrences of values or unique values in a data range
Let's say you want to find out how many unique values exist in a range that contains duplicate values. For example, if a column contains:
Show All
The values 5, 6, 7, and 6, then the result is three unique values--5 , 6 and 7.
The values "Buchanan", "Dodsworth", "Dodsworth", "Dodsworth", then the result is two unique values --"Buchanan" and "Dodsworth".
There are several ways to count unique values among duplicates.
What do you want to do?
Count the number of unique values by using a filter Count the number of unique values by using functions
Count the number of unique values by using a filter
You can use the Advanced Filter to extract the unique values from a column of data and paste them to a new location. Then you can use the ROWS function to count the number of items in the new range.
1. Ensure that the first row in the column has a column header. 2. On the DDaattaa menu, point to FFiilltteerr, and then click AAddvvaanncceedd FFiilltteerr. 3. In the AAddvvaanncceedd FFiilltteerr dialog box, click CCooppyy ttoo aannootthheerr llooccaattiioonn. 4. If the range that you are counting is not already selected, delete any information in the LLiisstt rraannggee box and
then click the column (or select the range) that contains your data. 5. In the CCooppyy ttoo box, delete any information in the box or click in the box, and then click a blank column where
you want to copy the unique values. 6. Select the UUnniiqquuee rreeccoorrddss oonnllyy check box, and click OOKK.
The unique values from the selected range are copied to the new column.
7. In the blank cell below the last cell in the range, enter the ROWS function. Use the range of unique values that you just copied as the argument. For example, if the range of unique values is B1:B45, then enter:
=ROWS(B1:B45)
Count the number of unique values by using functions
Use the IF, SUM< FREQUENCY, MATCH, and LEN functions to do this task:
Assign a value of 1 to each true condition by using the IF function. Add the total by using the SUM function. Count the number of unique values by using the FREQUENCY function. The FREQUENCY function ignores text and zero values. For the first occurrence of a specific value, this function returns a number equal to the number of occurrences of that value. For each occurrence of that same value after the first, this function returns a zero. Return the position of a text value in a range by using the MATCH function. This value returned is then used as an argument to the FREQUENCY function so that the corresponding text values can be evaluated. Find blank cells by using the LEN function. Blank cells have a length of 0.
Example
The example may be easier to understand if you copy it to a blank worksheet. How to copy an example
AA
11
DDaattaa
22
986
33
Dodsworth
BB DDaattaa Buchanan 563
Count occurrences of values or unique values in a data range - Excel -
44
67
55
66
Buchanan
77
689
88
Dodsworth
99
56
1100 67
789 235 Dodsworth 789 143 237 235
FFoorrmmuullaa
DDeessccrriippttiioonn (RReessuulltt)
=SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
Count the number of unique number values in cells A2:A10, but do not count blank cells or text values (4)
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
Count the number of unique text and number values in cells B2:B10 (which must not contain blank cells) (7)
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))
Count the number of unique text and number values in cells A2:A10 , but do not count blank cells or text values (6)
NOTES The formulas in this example must be entered as array formulas. Select each cell that contains a formula, press F2, and then press CTRL+SHIFT+ENTER. To see a function evaluated step by step, select the cell containing the formula. Then, on the TToooollss menu, point to FFoorrmmuullaa AAuuddiittiinngg and click EEvvaalluuaattee FFoorrmmuullaa.
Function details
FREQUENCY MATCH LEN, LENB SUM IF
Site Directory Contact Us Submit Feedback Legal Trademarks Privacy and Cookies Accessibility
? 2013 Microsoft Corporation. All rights reserved.
2 of 2
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- excel introduction to formulas shasta coe
- microsoft excel 2019 basic customguide
- microsoft excel 2013 functions university of reading
- excel formulas university of detroit mercy
- microsoft excel manual
- excel techniques to sum data with errors
- technology excel
- count occurrences of values or unique values in a data
Related searches
- python count occurrences of char in string
- python pandas count unique values in column
- count unique values in python
- unique values in a column pandas
- python count unique values in column
- count distinct values in a dataframe python
- unique values in a column python
- ms excel unique values in a column
- excel count unique items in a column
- count unique values in pandas
- pandas count values in a column
- count unique values in a list python