# Formula

#### raajnabriz

##### Board Regular
Please someone provide me with a formula to count a number from 1 to 1500. eg number 6 how many times it occurs, i need a formula

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
You need to be more specific, provide some examples of your input and what you expect as output.

Well the number 6 occurs 400 times inbetween 1 and 1500. Here's a formula to prove that.

Code:
``````Function COUNTINT(startVal As Long, endVal As Long, chk As Integer) As Variant

If chk > 9 Then COUNTINT = "1-9 only"

For x = startVal To endVal
For y = 1 To Len(x)
If Mid(x, y, 1) = chk Then
COUNTINT = COUNTINT + 1
End If
Next y
Next x

End Function``````

This would count 600 as 1, 660 as 2, and 666 as 3 occurrences.

Thanks NeonRedSharpie , but is there a simple excel formula instead of VBA?

Not that I can think of. This will be an excel formula if you throw it into a module. It can be called in the workbook.

I think this array-entered** formula will work for you...

=SUM(LEN(ROW(A\$1:A\$1500))-LEN(SUBSTITUTE(ROW(A\$1:A\$1500),6,"")))

Color Code
-------------------
Blue: Start Number
Green: End Number
Red: Number To Count

Thanks Mr. Rick, but the given formula isn't working.

Thanks Mr. Rick, but the given formula isn't working.

Describe "isn't working"... does it produce an error (if so, what is the error) or does it produce the wrong answer (if so, what do you think the answer is)?

EDIT NOTE
-------------------------
I just noticed I forgot to post what "array-entered" meant in my original message. You need to confirm the formula by pressing CTRL+SHIFT+ENTER and not just Enter by itself. Select the cell with the formula, click in the Formula Bar and then press CTRL+SHIFT+ENTER and I am guessing the formula will work for you then.

Last edited:
Excellent Mr. Rick, the CTRL+SHIFT+ENTER worked................thank you so much.

Replies
5
Views
177
Replies
9
Views
199
Replies
11
Views
433
Replies
3
Views
146
Replies
7
Views
216

1,220,980
Messages
6,157,185
Members
451,404
Latest member
Probe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back