PDA

View Full Version : Summing up values between 2 criteria in rows

Oran
03-06-2019, 07:38 AM
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.

03-06-2019, 07:17 PM
Hi Oran!
One question, Why does the first row contain Lett*, and the second row not contain Lett*?

Oran
03-06-2019, 11:34 PM
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.

03-06-2019, 11:53 PM
The position in the first post can't be seen clearly.
Can you give an attachment?

Oran
03-07-2019, 11:13 PM
Hi,

03-07-2019, 11:52 PM
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
03-10-2019, 09:27 AM
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

03-10-2019, 06:54 PM
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
03-10-2019, 08:10 PM
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