CODEWITHSUNDEEP
excelcountingvba
user3308733
user3308733

Reputation: 11

Counting the number of numbers in a range of cells

I am trying to find a formula in an Excel VBA that will count the number of numbers in a range of cells. I have tried a formula but it only counts the number of numbers within a single cell. Can anyone help me with this?

=IF(A1="",0,LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)

Upvotes: 1

Views: 628

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Paste this code in a module

Function GetNumbCount(Rng As Range) As Long
    Dim aCell As Range
    Dim MYAr As Variant
    Dim n As Long, i As Long

    For Each aCell In Rng
        If InStr(1, aCell.Value, " ") Then
            MYAr = Split(aCell.Value, " ")
            For i = LBound(MYAr) To UBound(MYAr)
                If IsNumeric(Trim(MYAr(i))) Then n = n + 1
            Next i
        Else
            If IsNumeric(Trim(aCell.Value)) Then n = n + 1
        End If
    Next

    GetNumbCount = n
End Function

And then use it in a worksheet

Syntax

GetNumbCount(Range)

ScreenShot

enter image description here

Upvotes: 2

V.B.
V.B.

Reputation: 6382

Cannot understand what is needed and why, but to sum the results of the given formula in the range A1:B2, you could use an array formula like this:

{=SUM(IF(A1:B2="",0,LEN(A1:B2)-LEN(SUBSTITUTE(A1:B2," ",""))+1))}

Use Ctrl+Shift+Enter to enter the formula as an array one

Upvotes: 0

Related Questions