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

COACHMAN99
COACHMAN99 used Ask the Experts™
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
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?

Please validate my assumptions.

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

Author

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.
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

Author

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

Author

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

Author

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

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial