Counting Digit 7 Occurrences from 0 to N with a Single Excel Formula

One of the most interesting intersections between mathematics and Excel is solving a counting problem without generating the actual numbers.

Consider the challenge:

How many times does the digit 7 appear in all integers from 0 to N?

For example:

N	Count of 7s
20	2
345	64
7890	3270

A brute-force solution would generate every number, convert them to text, and count the 7s. That works for small values but becomes inefficient for millions of numbers.

Instead, we can use mathematics.

The Formula

=LET(

    d,7,

    MAP(

        A2:A7,

        LAMBDA(n,

            SUM(

                MAP(

                    10^(SEQUENCE(LEN(n))-1),

                    LAMBDA(p,

                        INT((n+1)/(10*p))*p+

                        MIN(MAX(MOD(n+1,10*p)-d*p,0),p)

                    )

                )

            )

        )

    )

)

The formula calculates the answer directly without examining every number.

The Mathematical Idea

Rather than asking:

"How many 7s are there?"

Ask:

"How many 7s appear in each digit position?"

For example, for numbers from 0 to 999:

Units Position

7,17,27,37,47,57,67,77,87,97

107,117,... 

Every block of 10 numbers contributes one occurrence.

Total:

100 occurrences

Tens Position

70-79

170-179

270-279

...

Again:

100 occurrences

Hundreds Position

700-799 

Again:

100 occurrences

Total:

300 occurrences

Instead of examining 1000 numbers, we count digit patterns.

Step 1: Generate Place Values

The formula begins with:

10^(SEQUENCE(LEN(n))-1)

If:

n = 67369

then:

SEQUENCE(5)

returns

1

2

3

4

5

So:

10^(SEQUENCE(5)-1)

produces:

1

10

100

1000

10000

These represent:

Units

Tens

Hundreds

Thousands

Ten-thousands

Each position is processed independently.

Step 2: Complete Cycles

Consider:

INT((n+1)/(10*p))*p

Suppose:

n = 345

p = 10

(the tens position)

Then:

INT((346)/(100))*10

=30

Why?

Every 100-number block contains exactly 10 numbers whose tens digit is 7.

Example:

70-79

170-179

270-279

Three complete blocks contribute:

3 × 10 = 30

This counts all complete cycles.

Step 3: Partial Cycle

Complete cycles rarely reach exactly N.

The remaining numbers are handled by:

MIN(

    MAX(

        MOD(n+1,10*p)-d*p,

        0

    ),

    p

)

This looks intimidating, but it simply counts extra occurrences in the incomplete block.

Example

Let:

n = 345

d = 7

p = 10

Calculate:

MOD(346,100)

Result:

46

The incomplete block contains:

300-345

Numbers with tens digit 7 would start at:

370

which is beyond 345.

Therefore:

0 additional occurrences

The formula correctly returns zero.

Another Example

Let:

n = 378

p = 10

Then:

MOD(379,100)

returns:

79

The range includes:

370-378

with tens digit 7.

There are:

9

extra occurrences.

The second part of the formula captures exactly that amount.

Why d × p?

Notice:

d*p

For digit 7:

Position	p	d*p
Units	1	7
Tens	10	70
Hundreds	100	700

This identifies where the digit 7 begins in the current position.

Summing All Positions

The inner MAP calculates:

Units contribution+

Tens contribution+

hundreds contribution+

Thousands contribution

+ ...

using:

SUM(...)

For:

n = 67369

the calculation resembles:

Units* → 6737

Tens * →6730

Hundreds *→ 6700

Thousands *→ 6370

TenThousands* → 0

Total:

26537

which matches the expected answer.