Summing up values between 2 criteria in rows

[Oran]

Hi, my data looks like this:







Val 1
Val 2
Val 3
Val 4
Val 5
Total


cancer
…
…
Letting
…
2
1
4
6
5
13


toyota
carpool
..
..
Lettuce
5
2
1
3
4
6




I want to sum up values in each line that contains characters ca* and Lett*

Pleas assist.

[大灰狼1976]

Hi Oran!
One question, Why does the first row contain Lett*, and the second row not contain Lett*?

[Oran]

Hi ,
I want to sum up the values between two criteria:
Criteria 1: contains characters ca*
Criteria 2: contains characters lett*

line 1: sum up from val 1-val 4: Cancer...Letting
line 2: sum up from val 2-val 4: carpool...lettuce

Note: No the criteria is not in a ffixed cell. It keep changing.

[大灰狼1976]

The position in the first post can't be seen clearly.
Can you give an attachment?

[Oran]

Hi,

[大灰狼1976]

Hi Oran!
Although I did it, but there are more three questions:
Is there more then one "ca*" or "Lett*" in same row?
Is case sensitive?
Is there such a situation that "Lett*" in front of "Ca*"?

here's the code for you gave sample:

Sub test()
Dim arr, arrRst, i&, j&, sm&, b As Boolean, n&
arr = Sheets(1).Range("b3:l" & Sheets(1).[b65536].End(3).Row)
ReDim arrRst(1 To UBound(arr), 0)
For i = 1 To UBound(arr)
sm = 0
For j = 1 To 5
If arr(i, j) Like "ca*" Then b = True
If b Then sm = sm + arr(i, j + 5)
If arr(i, j) Like "Lett*" Then b = False: Exit For
Next j
If b = False And sm > 0 Then arrRst(i, 0) = sm
Next i
Sheets(1).[l3].Resize(i - 1) = arrRst
End Sub

[Oran]

Hi. Thank you so much.
1.Is there more then one "ca*" or "Lett*" in same row: No
2.Is case sensitive?-No
3. Is there such a situation that "Lett*" in front of "Ca*"?: No

[大灰狼1976]

2.
If arr(i, j) Like "ca*" Then b = True →　If UCase(arr(i, j)) Like "CA*" Then b = True
If arr(i, j) Like "Lett*" Then b = False: Exit For →　If UCase(arr(i, j)) Like "LETT*" Then b = False: Exit For

[Paul_Hossler]

I'm assuming that the data changed between the picture in Post #1 and the attachment in #5 since the numbers don't add

I'd just use an User Defined Function (UDF) with a ParamArray to allow a variable number of the 'match' values to be entered

23884




Option Explicit

Function CountAndSum(Crits As Range, Vals As Range, ParamArray MatchesVals()) As Long
Dim N As Long
Dim iMatches As Long, iCrit As Long

N = 0

For iMatches = LBound(MatchesVals) To UBound(MatchesVals)
For iCrit = 1 To Crits.Cells.Count
If UCase(Crits(iCrit)) Like UCase(MatchesVals(iMatches)) Then N = N + Vals(iCrit)
Next iCrit
Next iMatches
CountAndSum = N
End Function