Eric,
I'm not sure I entirely understand your example below. Your totals don't
seem to add up, and I'm not sure I understand what you mean by "The light
fixture types are drop downs that i made and are the Text examples that i
want to total based on the quantity cells." However, I'm going to give you
what I think you want. (Please note that the "#)" represent the row numbers.)
A
1) Area Light Fixture Type
2) 2nd Floor Bed Ceiling Mount Av.
3) 2nd Floor Closet Ceramic
4) 2nd Floor Hall Ceiling Mount Av.
5) Attic Ceramic
6) Marty's Office Floerescent 4'
B
1) Qty
2) 1
3) 2
4) 2
5) 3
6) 1
The next column is the text that you want to look for within these strings
C
1) Ceramic
2) Ceiling Mount
3)Flourescent 4'
D (formulas)
1) =SumIfString(C2, $A$2:$A$6, $B$2:$B$6)
2) =SumIfString(C3, $A$2:$A$6, $B$2:$B$6)
3) =SumIfString(C4, $A$2:$A$6, $B$2:$B$6)
D (results)
1) 5
2) 3
3) 1
Where the SumifString function is defined via VBA in a module in the
workbook as follows:
Function SumIfString(stringCheck As Range, descrRange As Range, sumRange As
Range)
Dim i As Range
SumIfString = 0
j = 1
For Each i In descrRange
If InStr(1, i.Value, stringCheck.Value) > 0 Then
SumIfString = SumIfString + sumRange.Cells(j).Value
End If
j = j + 1
Next i
End Function
(I'm hoping the word wrap on here didn't do funny things to my lines of
code.) Please note that this is a case dependent function. Although I have
the formulas in col D of the same worksheet you can put the function anywhere
in the worksheet that you like, and it will still work. In case you don't
know how to add the VBA code I have set up above... Tools => Macro => Visual
Basic Editor... Once there you can go to Insert => Module and add this
functions code in your new module. Hopefully this gets you a little closer
to your answer.
