Count Or Sum Specified Number In a Single Cell
Category: [Excel] Demo Available
Count Or Sum Specified Number In a Single Cell
Got any Excel/VBA Questions? Free Excel Help.
Count or Sum Specified Number In Single Cell Housing Many Numbers
Function Count_Sum_NumbersInCell(rCell As Range, _
sNumber As Single, strDelimeter, Optional bSum As Boolean) As Single
Dim vArray
Dim lLoop As Long
Dim sResult As Single
vArray = Split(rCell, strDelimeter)
If bSum = False Then
For lLoop = 0 To UBound(vArray)
If vArray(lLoop) = _
sNumber Then sResult = sResult + 1
Next lLoop
Else
With WorksheetFunction
For lLoop = 0 To UBound(vArray)
If vArray(lLoop) = _
sNumber Then sResult = .Sum(sResult, vArray(lLoop))
Next lLoop
End With
End If
Count_Sum_NumbersInCell = sResult
End Function
If A1 housed 1 3 30 3 23 3
Used in any cell as;
=Count_Sum_NumbersInCell(A1,3," ")
To count the number of 3's in A1 where numbers are separated by a space. 30 and 23 are not counted
=Count_Sum_NumbersInCell(A1,3," ",TRUE)
To sum the number 3's in A1 where numbers are separated by a space. 30 and 23 are not summed
See also:
| VBA Count of Each Item in a List |
| Count Distinct Values Once/Count Repeated Entries Only One Time |
| Custom Excel Function (UDF) that will Count Words in Excel |
Free Training Course: Lesson 1 - Excel Fundamentals
See also: Index to Excel VBA Code; Index to Excel Freebies; Lesson 1 - Excel Fundamentals; Index to how to… providing a range of solutions
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.