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:

Copy cell B9 and paste down as far as needed.

Array formula in cell C9:

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).

Get the Excel file

count-digits-in-ignoring-duplicates-in-any-line.xlsx

Count values category

Count unique distinct values that meet multiple criteria
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Count Conditionally Formatted cells
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Excel categories

Comments (2)