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

Capture.JPG

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