Sum numbers with specific letter and ignore all other cells

[abdullahmuthanna]

Hello guys

I want a formula to find the sum of numbers that contains specific letters



For Example :



1s 2s 4s lm 5s vt 55 Total: The sum of s





so I want the excel to ignore all cells without s and only calculate numbers with s

Note: There is no space between the number and s

 

[lrobbo314]

Using a VBA custom function.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
6	3	6s	xc	1s	3s	4s	5s	vt	lm	56	bm	32		19
Sheet3

Cell Formulas
Range	Formula
N6	N6	=SSUM(A6:L6)

Function SSUM(r As Range)
Dim AR() As Variant: AR = r.Value2

With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+)s"
    For i = 1 To UBound(AR, 2)
        Set mtch = .Execute(AR(1, i))
        If mtch.Count > 0 Then SSUM = SSUM + Int(mtch(0).submatches(0))
    Next i
End With

End Function