Avatar of COACHMAN99
COACHMAN99
 asked on

EXCEL SUM OF CELLS IN RANGE WHERE ADJACENT CELLS = ..

Hi All,
does anyone have a quick sample formula to derive the sum of cells in a colum where adjacent cells in the range meet certain conditions. (a bit like 'select sum(x) group by y where z=n and a=m etc')?
I need to code/run the formulae in VBA modules.
thanks
Microsoft Excel

Avatar of undefined
Last Comment
COACHMAN99

8/22/2022 - Mon
dlmille

Provide a sample dataset with sample conditions, and I can help.  Your example, >>  (a bit like 'select sum(x) group by y where z=n and a=m etc')

is a bit too generic for me to understand.  Its certainly easy to write a FUNCTION (a user defined function UDF) that you can specify a column/range, specify a parallel column which could be adjacent for testing.  Its the "Certain Conditions" that I'm having a problem with.

Do you mean to say you'd like to have a function like:

=SumColumnCondition(fRange as range, cRange as range, conditions as variant) as double

where you specify the first range, the second (adjacent range) and then perhaps a list of conditions for matching with the first range -

E.g.,

=SumColumnConditions(A1:A100,B1:B100,"Red,Green,Blue")

where the result would be the sum of column A1:A100 where B1:B100 has a Red, Green, or Blue text string in it?

Please validate my assumptions.

Dave
dlmille

Based on the assumptions, above, here's your function which you can run from VBA or use in the worksheet.

Code:
 
Function SumColumnCondition(fRange As Range, cRange As Range, strCond As Variant) As Double
Dim myCell As Range
Dim testRange As Range
Dim myStrs As Variant
Dim i As Integer, j As Long

    myStrs = Split(strCond, ",") 'comma delimited string, or only one test


    For Each myCell In fRange
        
        Debug.Print myCell.Address
        For i = LBound(myStrs) To UBound(myStrs)
            If InStr(cRange.Cells(j + 1, 1), myStrs(i)) > 0 Then 'found a match
                SumColumnCondition = SumColumnCondition + myCell.Value
            End If
        Next i
        j = j + 1
    Next myCell
    
End Function

Open in new window


See attached,

Enjoy!

Dave
conditionalSum-r1.xlsm
COACHMAN99

ASKER
hi dmlille
thanks for the detailed response.
Unfortunately I already have the code solution; I was hoping fo a 'formula' - iterating through rows takes too long.
cheers.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
dlmille

Please respond as to whether my assumptions are correct and I'll look at the formula solution possibilities.

Dave
ASKER CERTIFIED SOLUTION
Rob Henson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dlmille

COACH -

Here's a first pass at a formula-only solution.  Building the second, now:

=SUMPRODUCT(($C$12:$C$51)*(($D$12:$D$51="Dog")+($D$12:$D$51="Rabbit")+($D$12:$D$51="Zebra")))

Try this in the dataset I sent.

Dave
COACHMAN99

ASKER
how would the formula be phrased if I wanted the sum of col B where col A=2 and col C = 3 and col D = 4?
thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
dlmille

That becomes a MUCH simpler problem.  

Assumes header in row 1, and the data is down to 100

=SUMPRODUCT(($B$2:$B$100)*(($A$2:$A$100=2)+($C$2:$C$100=3)+($D$2:$D$100=4)))

dlmille


Correction:


=SUMPRODUCT(($B$2:$B$100)*($A$2:$A$100=2)*($C$2:$C$100=3)*($D$2:$D$100=4))

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
COACHMAN99

ASKER
thanks guys,
I will try with the formulae and get back if further issues arise.
have a good one!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dlmille

COACH - just for future and no big worries..

Your original question:  >>where adjacent cells in the range meet certain conditions. (a bit like 'select sum(x) group by y where z=n and a=m etc')? <<-potentially confusing


had different implications than your clarifying comment (which took me all of 20 seconds to understand):
>>how would the formula be phrased if I wanted the sum of col B where col A=2 and col C = 3 and col D = 4?  <<- well written with clear criteria


Dave
COACHMAN99

ASKER
thanks Dave
my apologies, I verbalized it after an 18-hour day :), and was trying to express it as SQL
cheers.