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

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

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

Select allOpen in new window

See attached,

Enjoy!

Dave
conditionalSum-r1.xlsm

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.

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

Dave

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

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

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

Correction:


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

thanks guys,
I will try with the formulae and get back if further issues arise.
have a good one!

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

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