dependant summing

[greg]

Hello,
I would like to sum some numbers. But depending on 2 other column.
Say I have 3 named ranges.
the A column, B column and c column. = Range1, Range2, Range3

Range3 (column C) has numbers. Range 1, 2 are strings.

If I wanted to sum numbers in Range3 when Range1 = "ABC" and Range2 = "DEF"

do you use Sum (Range1="ABC....
Or do I need If statments?

thanks for any help

 

[Bob Phillips]

=SUMPRODUCT(--(Range1="ABC"),--(Range2="DEF"),Range3)

 

[Mike]

this is an array formula, so press Ctrl+Shift+Enter after pasting it into a
cell
=SUM((A1:A10="ABC")*(B1:B10="DEF")*C1:C10)

 

[greg]

thanks for the help.
If I wanted to move this to vba code. I would think something like this
would work.
However, I get an object does not support this method.

Any ideas on moving to VBA?


Sub test()

Dim x
x = Excel.WorksheetFunction.SumProduct(--(Excel.ActiveWorkbook("Range1") =
"ABC"), --(Excel.ActiveWorkbook("Range2") = "DEF"),
Excel.ActiveWorkbook("Range3"))

End Sub