How to count & sum unique cell values in a mixture of duplicate values in Excel sheet column

Updated on October 11, 2012

If you have a list of values in a excel column which include duplicate values of number, text and blanks and you want to count the total unique values, you can do that by using various functions available in Microsoft excel. This can also be achieved by using filters but I prefer using functions as they are easier to use and also the whole exercise can be done neatly.

In the video, we will use five functions to get the desired result.

1. Sum - As you know, it sums up the numerical values.

2. IF - It evaluates a condition and return 1 if true, otherwise false

3. FREQUENCY - It calculates the occurrence of first number in a given range of values. It ignore 0 and texts.

4. MATCH- It matched a particular value in a set and returns its position.

5. LEN - Returns the length of a text.

Suppose If you have a list like this:

A

1 7

2 5

3 9

4 3

5 2

6. BLANK

7 HELLO

8 4

9 Navi

10 BLANK

11 3

• So if you want to count the unique numbers and DONOT want to count text or BLANK cells, the you can use the formula

=SUM(IF(FREQUENCY (A1:A11,A1:A11)>0,1))

See more details in the video

• If you want to want to also include unique text in your search and leave out the blanks then you will have to add MATCH function like below:

=SUM(IF(FREQUENCY(MATCH(A1:A11,A1:A11,0),MATCH(A1:A11,A1:A11,0))>0,1))

However in order to use the above formula, please make sure there are no blanks in the column otherwise it will give error.

• If you have blanks in your columns and you want to count unique texts and number and NOT blanks, you will have to add a formula LEN which will calculate blanks .

=SUM(IF(FREQUENCY(IF(LEN(A1:A11)>0,MATCH(A1:A11,A1:A11,0),"),IF(LEN(A1:A11)>0,MATCH(A1:A11,A1:A11,0),"))>0,1))

Each of above formula should be entered as array formulas. So please include the formula under ={} or click on the formula cell and press F2 and the ctr+shift+Enter.

Ex- The first formula then look like:

{=SUM(IF(FREQUENCY (A1:A11,A1:A11)>0,1))}

Popular

0

48

• How to create a thematic or Choropleth map in Excel 2007 and Excel 2010

4

0 of 8192 characters used