## Count digits and ignore duplicates in any line in excel

### Question:

I have a question that I can’t seem to find an answer to:

I want to make a full count of digits 0 to 9 while ignoring duplicates in any line

B C D E

5 5 5 5

8 6 7 4

6 2 8 7

7 7 1 6

5 6 6 2

For example, with the digits shown above, my results for the count will be:

0=0; 1=1; 2=2; 3=0; 4=1; 5=5; 6=4; 7=3; 8=2; 9=0

The formula below does not work for the “5” count since it counts all the occurrences:

=COUNTIF($B$1:$E$5,5)

### Answer:

I can´t achieve the desired result for number 5. I calculated any line as "in any row" and "in any column". I guess the desired result for number 5 is a typo.

**Array formula in cell B9:**

=SUM(IF(FREQUENCY(IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, ""), ROW($1:$5))>0, 1, 0)) + CTRL + SHIFT + ENTER.

Copy cell B9 and paste down as far as needed.

**Array formula in cell C9:**

=SUM(IF(FREQUENCY(IF(A9=$B$1:$E$5, COLUMN($B$1:$E$5)-MIN(COLUMN($B$1:$E$5))+1, ""), ROW($1:$4))>0, 1, 0)) + CTRL + SHIFT + ENTER.

Copy cell C9 and paste down as far as needed.

### How the formula works in cell B9

*Step 1 - Find digit in array*

=SUM(IF(FREQUENCY(IF(**A9=$B$1:$E$5**, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, ""), ROW($1:$5))>0, 1, 0))

A9=$B$1:$E$5

becomes

0={5, 5, 5, 5;8, 6, 7, 4;6, 2, 8, 7;7, 7, 1, 6;5, 6, 6, 2}

becomes

{FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}

*Step 2 - Convert boolean array to row numbers*

=SUM(IF(FREQUENCY(**IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, "")**, ROW($1:$5))>0, 1, 0))

IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, "")

becomes

IF({FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE}, {1, 2, 3, 4}, "")

becomes

{"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", ""}

*Step 3 - Count row numbers*

=SUM(IF(**FREQUENCY(IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, ""), ROW($1:$5))**>0, 1, 0))

FREQUENCY(data_array, bins_array)*
*Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array

*.*

FREQUENCY(IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, ""), ROW($1:$5))

becomes

FREQUENCY({"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", "";"", "", "", ""}, {1; 2; 3; 4; 5})

becomes

{0; 0; 0; 0; 0}

*Step 4 - Count numbers larger than zero*

=SUM(IF(FREQUENCY(IF(A9=$B$1:$E$5, ROW($B$1:$E$5)-MIN(ROW($B$1:$E$5))+1, ""), ROW($1:$5))>0, 1, 0))

becomes

=SUM(IF({0; 0; 0; 0; 0}>0, 1, 0))

becomes

=SUM({0; 0; 0; 0; 0}) and returns 0 (zero).

### Download excel sample file

count digits in ignoring duplicates in any line.xlsx

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**IF(**logical_test,[value_if_true], [value_if_false]**) **Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

**FREQUENCY**(data_array, bins_array)*
*Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than Bins_array

*.*

**ROW(**reference**)** returns the rownumber of a reference

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

### Category: Count values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: Table of Contents Count […]

Comments(26) Filed in category: Count values, Excel, Unique distinct values, Unique values

Someone googled "Count records between date range" and landed on my website. I realize I have not covered this problem. […]

Comments(22) Filed in category: Count values, Dates, Excel

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]

Comments(19) Filed in category: Count values, Excel

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]

Comments(16) Filed in category: Count values, Excel, Range

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

Comments(16) Filed in category: Count values, Excel, Frequency, User defined functions (udf), VBA

Question: I have values in two not adjacent columns. I want to know how many unique distinct values there are […]

Comments(14) Filed in category: Count values, Excel, Unique distinct values

Sam asks in this post: Count records between two dates in excel Any chance this would work with multiple parameters. […]

Comments(14) Filed in category: Count values, Dates, Excel

Table of Contents Repeat values Repeat the range according to criteria in loop BatTodor asks: I failed to find right […]

Comments(13) Filed in category: Count values, Excel

### 2 Responses to “Count digits and ignore duplicates in any line in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

I know a formulas

1*30*(5+7)/2*(0.90+0.70+0.60)/3 =

Please send a formula in my email

January 17th, 2012 at 7:26 pm

I know a formulas

1*30*(5+7)/2*(0.90+0.70+0.60)/3 =

Please send a formula in my email