Total amount By word

[art]

Hello:

I have the following chart:
A
1 1 Plastic 2 Wood 1 metal
2 5 Plastic 2 Metal
3 4 Metal 3 Wood

My question is, what is the easiest way to get a total of the amount of
plastic in the whole column A? The total should be 6.

P.S. (In Cell A1 it says the whole thing, "1 Plastic 2 Wood 1 metal" and so
forth in each cell.)

Any help would be appriciated.

Thanks.

Art.

 

[Jacob Skaria]

Another UDF 'art' ...Assume your data will be in the same format. Numeric
value followed by text and each piece separated by space.....OR otherwise the
below UDF will return an error....

=Getcountfor(A1:A3,"plastic")

Function GetCountfor(varRange As Range, strSearch As String)
Dim varTemp As Range
Dim arrData As Variant
For Each varTemp In varRange
arrData = Split(varTemp.Text, " ")
For intTemp = 0 To UBound(arrData)
If UCase(Trim(arrData(intTemp))) = UCase(strSearch) Then
GetCountfor = GetCountfor + CInt("0" & arrData(intTemp - 1))
End If
Next
Next
End Function

 

[Rick Rothstein]

A similar approach to the one you used, but I believe this coding may be a
little bit more efficient...

Function GetCountFor(varRange As Range, strSearch As String) As Double
Dim X As Long
Dim C As Range
Dim Parts() As String
For Each C In varRange
Parts = Split(C.Value, " ")
For X = 1 To UBound(Parts) Step 2
If StrComp(Parts(X), strSearch, vbTextCompare) = 0 Then _
GetCountFor = GetCountFor + Parts(X - 1)
Next
Next
End Function