Nested SumIf Excel2003 VBA

[tony.edge715]

I want to add up the totals in a column based on matching criteria in two other columns. I have been able to sum the column based on one criterial column using SumIf but can't make it work for 2 columns.

Type Origin Weight

Fruit UK 20
Veg UK 15
Fruit Spain 25

I have named ranges so for one criteria sumif(Range("Type"),"UK",Range("Weight")) would return 35. what I want is the weight of Fruit from the UK. In excel I would use array function sum(if(Type="Fruit",if(Origin="UK",Weight))). How do I do it in VBA? Thanks

 

[Marcelo Branco]

Hi,

Maybe something like

Sub test01()
    Dim myVar As Long
 
    myVar = Evaluate("=SUMPRODUCT(--(Type = ""Fruit""), --(Origin =""UK""), Weight)")
 
    MsgBox myVar
End Sub

 

[tony.edge715]

Thanks for the reply. Not got it working yet but did not know about "Evaluate". Had a search on the web - very useful command!