# 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

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

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
``````

See attached,

Enjoy!

Dave
conditionalSum-r1.xlsm
COACHMAN99

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
Rob Henson

Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
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.
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

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
Get an unlimited membership to EE for less than \$4 a week.
Unlimited question asking, solutions, articles and more.
COACHMAN99

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