# EXCEL SUM OF CELLS IN RANGE WHERE ADJACENT CELLS = .. on
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
Comment
Watch Question

Do more with EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2012

Commented:
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?

Dave
Most Valuable Expert 2012
Top Expert 2012

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

Commented:
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.
Most Valuable Expert 2012
Top Expert 2012

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

Dave
Finance Analyst
Commented:
What version of Excel are you using??

Excel 2007 or later you can use the SUMIFS function.

Excel 2003 or earlier you would have to use the SUMPRODUCT function as SUMIF only allows one criteria.

Thanks
Rob H
Most Valuable Expert 2012
Top Expert 2012

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

Commented:
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
Most Valuable Expert 2012
Top Expert 2012

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

Most Valuable Expert 2012
Top Expert 2012

Commented:

Correction:

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

Most Valuable Expert 2012
Top Expert 2012
Commented:
If you have Excel 2007+, this can be rephrased as:

=SUMIFS(\$B\$2:\$B\$100,\$A\$2:\$A\$100,2,\$C\$2:\$C\$100,3,\$D\$2:\$D\$100,4)

Cheers,

Dave

Commented:
thanks guys,
I will try with the formulae and get back if further issues arise.
have a good one!
Most Valuable Expert 2012
Top Expert 2012

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

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

Do more with 