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

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

03-06-2019, 07:17 PM

Hi Oran!

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

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

03-06-2019, 11:53 PM

The position in the first post can't be seen clearly.

Can you give an attachment?

大灰狼1976

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

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

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.