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

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

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

Code:

See attached,

Enjoy!

Dave

conditionalSum-r1.xlsm

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

See attached,

Enjoy!

Dave

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.

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

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

Dave

Dave

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

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

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

=SUMPRODUCT(($C$12:$C$51)*

Try this in the dataset I sent.

Dave

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

thanks

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

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

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

=SUMPRODUCT(($B$2:$B$100)*

Correction:

=SUMPRODUCT(($B$2:$B$100)*

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

thanks guys,

I will try with the formulae and get back if further issues arise.

have a good one!

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

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

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

thanks Dave

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

cheers.

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

cheers.

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

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:A1

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