How to count and sum numerical values automatically from a set of data containing strings using a macro. Watch the video below:

If you look at the image below containing strings (text) and numerical values in column F for example, you will wonder how we can get the numerical data from rows 8 to 14.

The user wishes to count and total the values from the last row in column F to the point where she encounters a string. The string can be anywhere in row 8 or row 12, for example. If you use the simple COUNTA formula, we will get the total of the cells containing data in column F. If we use the COUNTIF formula, we will get all the cells containing numerical values.

=COUNTA(F1:F14) = 14

=COUNTIF(F1:F14,”>=0″) = 10

So how do we solve the user’s problem? We can use a reverse loop in a macro. The complete VBA code is given below:

Sub CountSumNumericalValuesTillString()

Dim i As Long, lastrow As Long, sum As Long, count As Long
count = 0
sum = 0
lastrow = Sheet1.Cells(Rows.count, 6).End(xlUp).Row

For i = lastrow To 2 Step -1
If IsNumeric(Cells(i, 6)) Then
count = count + 1
sum = sum + Cells(i, 6)

Else
    MsgBox "The count is " & count & vbCrLf & "The sum is " & sum
    Exit Sub
End If

Next i

End Sub

To find the lastrow used in a set of data we can also use the following VBA code:

Sub mylastrow()
Dim lastrow As Long
lastrow = Application.WorksheetFunction.CountA(Range(“F:F”))
MsgBox lastrow
End Sub