Formula

[raajnabriz]

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

 

[Special-K99]

You need to be more specific, provide some examples of your input and what you expect as output.

 

[NeonRedSharpie]

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

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.

 

[raajnabriz]

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

 

[NeonRedSharpie]

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.

 

[Rick Rothstein]

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

 

[raajnabriz]

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

 

[Rick Rothstein]

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.

 

[raajnabriz]

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