Sumproduct in VBA
 

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

Sumproduct in VBA
 

msgbox activesheet.evaluate _
("SUMPRODUCT((I341:I352="" International "")" & _
"*(H341:H352="" Shipper ""),(T341:T352))")



Rafi wrote:

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

--

Dave Peterson

Sumproduct in VBA
 

Great !! can you help me with the correct syntax for converting the fixed
cell range (e.g. i342:I352) to a dynamic range where the row numbers are
stored in variables intFirstRow and intLastRow


"Rafi" wrote:

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

Sumproduct in VBA
 

dim myRng as range
dim intFirstRow as long
dim intLastRow as long

intfirstrow = 13
intlastrow = 9923

with worksheets("sheet9999")
set myrng = .range("I" & intfirstrow, "I" & intlastrow)
'or
set myrng = .range(.cells(intfirstrow,"I"),.cells(intlastrow," I"))
'or
set myrng = .range("I" & intfirstrow).resize(intlastrow-intfirstrow+1)
'or
set myrng = .cells(intfirstrow,"I").resize(intlastrow-intfirstrow+1)
end with



Rafi wrote:

Great !! can you help me with the correct syntax for converting the fixed
cell range (e.g. i342:I352) to a dynamic range where the row numbers are
stored in variables intFirstRow and intLastRow

"Rafi" wrote:

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

--

Dave Peterson

Sumproduct in VBA
 

Dave,

I should have been clearer with my last question. I need help with using a
the variable myng in the context of the Sumproduct formula you gave me. If I
simply replace I314:i352 = ""International"" with myrng = ""International"" I
get an error.

Thanks again

"Rafi" wrote:

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

Sumproduct in VBA
 

Option Explicit
Sub testme01()
Dim myRng1 As Range
Dim myRng2 As Range
Dim myVal As Double
Dim myFormula As String

With ActiveSheet
Set myRng1 = .Range("I341:I352")
Set myRng2 = .Range("H341").Resize(myRng1.Rows.Count)
'they have to be the same size, right?
End With

myFormula = "sumproduct(" _
& "--(" & myRng1.Address(external:=True) _
& "="" International "")," _
& "--(" & myRng2.Address(external:=True) _
& "="" Shipper ""))"

myVal = Application.Evaluate(myFormula)

MsgBox myVal
End Sub

If I understand what you meant...


Rafi wrote:

Dave,

I should have been clearer with my last question. I need help with using a
the variable myng in the context of the Sumproduct formula you gave me. If I
simply replace I314:i352 = ""International"" with myrng = ""International"" I
get an error.

Thanks again

"Rafi" wrote:

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

--

Dave Peterson

Sumproduct in VBA
 

Dave,

Thanks for all the help so far. enclosed below you will see a copy of my
current code with which I have two problems:

- I am getting a Run-Time error 13: "Type Mismatch" error
- Once that is fixed I could use your help in getting the right syntax for
replacing the range T43:V45 (last line of myFormula = ) with a variable
such as myRng3.

Thanks

Option Explicit
Sub SumP()

Dim territory As String
Dim LastRow As Long
Dim FirstRow As Long
Dim Tbl As Range
Dim myRng1 As Range
Dim myRng2 As Range
Dim myVal As Double
Dim myFormula As String
territory = InputBox("Region/District/Territory as 10000000 (Region 10)")
Set Tbl = ActiveCell.CurrentRegion
Tbl.Offset(1, 0).Resize(Tbl.Rows.Count, _
Tbl.Columns.Count).Select

Selection.Find(What:=territory, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
FirstRow = ActiveCell.Row

Do While ActiveCell.Value = territory
ActiveCell.Offset(1, 0).Activate
LastRow = ActiveCell.Row - 1
Loop

With ActiveSheet
Set myRng1 = .Range("G" & FirstRow, "G" & LastRow)
Set myRng2 = .Range("H" & FirstRow, "H" & LastRow)
End With

myFormula = "sumproduct(" _
& "--(" & myRng1.Address(external:=True) _
& "="" Other "")*" _
& "--(" & myRng2.Address(external:=True) _
& "="" Shipper "")),(T43:V45)"

myVal = Application.Evaluate(myFormula)

End Sub

"Dave Peterson" wrote:

Option Explicit
Sub testme01()
Dim myRng1 As Range
Dim myRng2 As Range
Dim myVal As Double
Dim myFormula As String

With ActiveSheet
Set myRng1 = .Range("I341:I352")
Set myRng2 = .Range("H341").Resize(myRng1.Rows.Count)
'they have to be the same size, right?
End With

myFormula = "sumproduct(" _
& "--(" & myRng1.Address(external:=True) _
& "="" International "")," _
& "--(" & myRng2.Address(external:=True) _
& "="" Shipper ""))"

myVal = Application.Evaluate(myFormula)

MsgBox myVal
End Sub

If I understand what you meant...


Rafi wrote:

Dave,

I should have been clearer with my last question. I need help with using a
the variable myng in the context of the Sumproduct formula you gave me. If I
simply replace I314:i352 = ""International"" with myrng = ""International"" I
get an error.

Thanks again

"Rafi" wrote:

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

--

Dave Peterson

Sumproduct in VBA
 

I'm confused about what you're doing, but that last range (T43:t45) will want to
be the same dimensions as the other ranges (x rows by 1 column).

And it sure looks like you may want xlwhole (not xlpart) to find the correct
territory. If you use part and find a partial match, will your figures be
correct.

And I don't trust using the activecell. If the activecell is x9999 (for
example) and that is way far away from your data, then the whole thing blows
up. Can you use A1 instead?

And I modified the code to search for the territory in just one column (I used
the first column in my sample). I would think that the layout of your data
would have the territory code in a single column.

Anyway, here's something to play for awhile.

Option Explicit
Sub SumP()

Dim Territory As String
Dim Tbl As Range
Dim myRngG As Range
Dim myRngH As Range
Dim myRngI As Range
Dim myVal As Double
Dim myFormula As String
Dim TopCell As Range
Dim BotCell As Range

Territory = InputBox("Region/District/Territory as 10000000 (Region 10)")

With ActiveSheet
'I don't trust the where the activecell is
'can you use A1 instead?
Set Tbl = .Range("a1").CurrentRegion
'come down one row
'reduce the number of rows by 1 and
'don't touch the number of columns
Set Tbl = Tbl.Offset(1, 0).Resize(Tbl.Rows.Count - 1)

'which column has the territory field?
'I used column A
With Tbl.Columns(1)
Set TopCell = .Cells.Find(What:=Territory, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Set BotCell = .Cells.Find(What:=Territory, _
After:=.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)

End With

If TopCell Is Nothing Then
MsgBox "Territory: " & Territory & " wasn't found"
Exit Sub
End If

Set myRngG = .Range(.Cells(TopCell.Row, "G"), .Cells(BotCell.Row, "G"))
'I could have used the same syntax as myRngG above, but it's just
'one (or two) columns over, so I used .offset().
Set myRngH = myRngG.Offset(0, 1)
Set myRngI = myRngG.Offset(0, 2)


myFormula = "sumproduct(" _
& "--(" & myRngG.Address(external:=True) & "="" Other "")," _
& "--(" & myRngH.Address(external:=True) & "="" Shipper "")," _
& "(" & myRngI.Address(external:=True) & "))"

myVal = Application.Evaluate(myFormula)

MsgBox myVal

End With

End Sub

(I changed the way to find the top and bottom cells with the territory. This
works ok if the territory codes are nicely sorted. But I'm not sure if your
territory data is contiguous.)

And I changed the names of the ranges. myRngG makes more sense to me when I'm
reading the code--it means the junk in column G.


Rafi wrote:

Dave,

Thanks for all the help so far. enclosed below you will see a copy of my
current code with which I have two problems:

- I am getting a Run-Time error 13: "Type Mismatch" error
- Once that is fixed I could use your help in getting the right syntax for
replacing the range T43:V45 (last line of myFormula = ) with a variable
such as myRng3.

Thanks

Option Explicit
Sub SumP()

Dim territory As String
Dim LastRow As Long
Dim FirstRow As Long
Dim Tbl As Range
Dim myRng1 As Range
Dim myRng2 As Range
Dim myVal As Double
Dim myFormula As String
territory = InputBox("Region/District/Territory as 10000000 (Region 10)")
Set Tbl = ActiveCell.CurrentRegion
Tbl.Offset(1, 0).Resize(Tbl.Rows.Count, _
Tbl.Columns.Count).Select

Selection.Find(What:=territory, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
FirstRow = ActiveCell.Row

Do While ActiveCell.Value = territory
ActiveCell.Offset(1, 0).Activate
LastRow = ActiveCell.Row - 1
Loop

With ActiveSheet
Set myRng1 = .Range("G" & FirstRow, "G" & LastRow)
Set myRng2 = .Range("H" & FirstRow, "H" & LastRow)
End With

myFormula = "sumproduct(" _
& "--(" & myRng1.Address(external:=True) _
& "="" Other "")*" _
& "--(" & myRng2.Address(external:=True) _
& "="" Shipper "")),(T43:V45)"

myVal = Application.Evaluate(myFormula)

End Sub

"Dave Peterson" wrote:

Option Explicit
Sub testme01()
Dim myRng1 As Range
Dim myRng2 As Range
Dim myVal As Double
Dim myFormula As String

With ActiveSheet
Set myRng1 = .Range("I341:I352")
Set myRng2 = .Range("H341").Resize(myRng1.Rows.Count)
'they have to be the same size, right?
End With

myFormula = "sumproduct(" _
& "--(" & myRng1.Address(external:=True) _
& "="" International "")," _
& "--(" & myRng2.Address(external:=True) _
& "="" Shipper ""))"

myVal = Application.Evaluate(myFormula)

MsgBox myVal
End Sub

If I understand what you meant...


Rafi wrote:

Dave,

I should have been clearer with my last question. I need help with using a
the variable myng in the context of the Sumproduct formula you gave me. If I
simply replace I314:i352 = ""International"" with myrng = ""International"" I
get an error.

Thanks again

"Rafi" wrote:

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

--

Dave Peterson


--

Dave Peterson

Sumproduct in VBA
 

Dave,

It worked like a charm and I also learned a few things in the process. Can
you recommend a good Excel VBA reference book?

"Dave Peterson" wrote:

I'm confused about what you're doing, but that last range (T43:t45) will want to
be the same dimensions as the other ranges (x rows by 1 column).

And it sure looks like you may want xlwhole (not xlpart) to find the correct
territory. If you use part and find a partial match, will your figures be
correct.

And I don't trust using the activecell. If the activecell is x9999 (for
example) and that is way far away from your data, then the whole thing blows
up. Can you use A1 instead?

And I modified the code to search for the territory in just one column (I used
the first column in my sample). I would think that the layout of your data
would have the territory code in a single column.

Anyway, here's something to play for awhile.

Option Explicit
Sub SumP()

Dim Territory As String
Dim Tbl As Range
Dim myRngG As Range
Dim myRngH As Range
Dim myRngI As Range
Dim myVal As Double
Dim myFormula As String
Dim TopCell As Range
Dim BotCell As Range

Territory = InputBox("Region/District/Territory as 10000000 (Region 10)")

With ActiveSheet
'I don't trust the where the activecell is
'can you use A1 instead?
Set Tbl = .Range("a1").CurrentRegion
'come down one row
'reduce the number of rows by 1 and
'don't touch the number of columns
Set Tbl = Tbl.Offset(1, 0).Resize(Tbl.Rows.Count - 1)

'which column has the territory field?
'I used column A
With Tbl.Columns(1)
Set TopCell = .Cells.Find(What:=Territory, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Set BotCell = .Cells.Find(What:=Territory, _
After:=.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)

End With

If TopCell Is Nothing Then
MsgBox "Territory: " & Territory & " wasn't found"
Exit Sub
End If

Set myRngG = .Range(.Cells(TopCell.Row, "G"), .Cells(BotCell.Row, "G"))
'I could have used the same syntax as myRngG above, but it's just
'one (or two) columns over, so I used .offset().
Set myRngH = myRngG.Offset(0, 1)
Set myRngI = myRngG.Offset(0, 2)


myFormula = "sumproduct(" _
& "--(" & myRngG.Address(external:=True) & "="" Other "")," _
& "--(" & myRngH.Address(external:=True) & "="" Shipper "")," _
& "(" & myRngI.Address(external:=True) & "))"

myVal = Application.Evaluate(myFormula)

MsgBox myVal

End With

End Sub

(I changed the way to find the top and bottom cells with the territory. This
works ok if the territory codes are nicely sorted. But I'm not sure if your
territory data is contiguous.)

And I changed the names of the ranges. myRngG makes more sense to me when I'm
reading the code--it means the junk in column G.


Rafi wrote:

Dave,

Thanks for all the help so far. enclosed below you will see a copy of my
current code with which I have two problems:

- I am getting a Run-Time error 13: "Type Mismatch" error
- Once that is fixed I could use your help in getting the right syntax for
replacing the range T43:V45 (last line of myFormula = ) with a variable
such as myRng3.

Thanks

Option Explicit
Sub SumP()

Dim territory As String
Dim LastRow As Long
Dim FirstRow As Long
Dim Tbl As Range
Dim myRng1 As Range
Dim myRng2 As Range
Dim myVal As Double
Dim myFormula As String
territory = InputBox("Region/District/Territory as 10000000 (Region 10)")
Set Tbl = ActiveCell.CurrentRegion
Tbl.Offset(1, 0).Resize(Tbl.Rows.Count, _
Tbl.Columns.Count).Select

Selection.Find(What:=territory, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
FirstRow = ActiveCell.Row

Do While ActiveCell.Value = territory
ActiveCell.Offset(1, 0).Activate
LastRow = ActiveCell.Row - 1
Loop

With ActiveSheet
Set myRng1 = .Range("G" & FirstRow, "G" & LastRow)
Set myRng2 = .Range("H" & FirstRow, "H" & LastRow)
End With

myFormula = "sumproduct(" _
& "--(" & myRng1.Address(external:=True) _
& "="" Other "")*" _
& "--(" & myRng2.Address(external:=True) _
& "="" Shipper "")),(T43:V45)"

myVal = Application.Evaluate(myFormula)

End Sub

"Dave Peterson" wrote:

Option Explicit
Sub testme01()
Dim myRng1 As Range
Dim myRng2 As Range
Dim myVal As Double
Dim myFormula As String

With ActiveSheet
Set myRng1 = .Range("I341:I352")
Set myRng2 = .Range("H341").Resize(myRng1.Rows.Count)
'they have to be the same size, right?
End With

myFormula = "sumproduct(" _
& "--(" & myRng1.Address(external:=True) _
& "="" International "")," _
& "--(" & myRng2.Address(external:=True) _
& "="" Shipper ""))"

myVal = Application.Evaluate(myFormula)

MsgBox myVal
End Sub

If I understand what you meant...


Rafi wrote:

Dave,

I should have been clearer with my last question. I need help with using a
the variable myng in the context of the Sumproduct formula you gave me. If I
simply replace I314:i352 = ""International"" with myrng = ""International"" I
get an error.

Thanks again

"Rafi" wrote:

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

--

Dave Peterson


--

Dave Peterson

Sumproduct in VBA
 

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

Depending on how advanced you are...

Professional Excel Development
By Stephen Bullen, Rob Bovey, John Green

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.

Rafi wrote:

Dave,

It worked like a charm and I also learned a few things in the process. Can
you recommend a good Excel VBA reference book?

"Dave Peterson" wrote:

I'm confused about what you're doing, but that last range (T43:t45) will want to
be the same dimensions as the other ranges (x rows by 1 column).

And it sure looks like you may want xlwhole (not xlpart) to find the correct
territory. If you use part and find a partial match, will your figures be
correct.

And I don't trust using the activecell. If the activecell is x9999 (for
example) and that is way far away from your data, then the whole thing blows
up. Can you use A1 instead?

And I modified the code to search for the territory in just one column (I used
the first column in my sample). I would think that the layout of your data
would have the territory code in a single column.

Anyway, here's something to play for awhile.

Option Explicit
Sub SumP()

Dim Territory As String
Dim Tbl As Range
Dim myRngG As Range
Dim myRngH As Range
Dim myRngI As Range
Dim myVal As Double
Dim myFormula As String
Dim TopCell As Range
Dim BotCell As Range

Territory = InputBox("Region/District/Territory as 10000000 (Region 10)")

With ActiveSheet
'I don't trust the where the activecell is
'can you use A1 instead?
Set Tbl = .Range("a1").CurrentRegion
'come down one row
'reduce the number of rows by 1 and
'don't touch the number of columns
Set Tbl = Tbl.Offset(1, 0).Resize(Tbl.Rows.Count - 1)

'which column has the territory field?
'I used column A
With Tbl.Columns(1)
Set TopCell = .Cells.Find(What:=Territory, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Set BotCell = .Cells.Find(What:=Territory, _
After:=.Cells(1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)

End With

If TopCell Is Nothing Then
MsgBox "Territory: " & Territory & " wasn't found"
Exit Sub
End If

Set myRngG = .Range(.Cells(TopCell.Row, "G"), .Cells(BotCell.Row, "G"))
'I could have used the same syntax as myRngG above, but it's just
'one (or two) columns over, so I used .offset().
Set myRngH = myRngG.Offset(0, 1)
Set myRngI = myRngG.Offset(0, 2)


myFormula = "sumproduct(" _
& "--(" & myRngG.Address(external:=True) & "="" Other "")," _
& "--(" & myRngH.Address(external:=True) & "="" Shipper "")," _
& "(" & myRngI.Address(external:=True) & "))"

myVal = Application.Evaluate(myFormula)

MsgBox myVal

End With

End Sub

(I changed the way to find the top and bottom cells with the territory. This
works ok if the territory codes are nicely sorted. But I'm not sure if your
territory data is contiguous.)

And I changed the names of the ranges. myRngG makes more sense to me when I'm
reading the code--it means the junk in column G.


Rafi wrote:

Dave,

Thanks for all the help so far. enclosed below you will see a copy of my
current code with which I have two problems:

- I am getting a Run-Time error 13: "Type Mismatch" error
- Once that is fixed I could use your help in getting the right syntax for
replacing the range T43:V45 (last line of myFormula = ) with a variable
such as myRng3.

Thanks

Option Explicit
Sub SumP()

Dim territory As String
Dim LastRow As Long
Dim FirstRow As Long
Dim Tbl As Range
Dim myRng1 As Range
Dim myRng2 As Range
Dim myVal As Double
Dim myFormula As String
territory = InputBox("Region/District/Territory as 10000000 (Region 10)")
Set Tbl = ActiveCell.CurrentRegion
Tbl.Offset(1, 0).Resize(Tbl.Rows.Count, _
Tbl.Columns.Count).Select

Selection.Find(What:=territory, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
FirstRow = ActiveCell.Row

Do While ActiveCell.Value = territory
ActiveCell.Offset(1, 0).Activate
LastRow = ActiveCell.Row - 1
Loop

With ActiveSheet
Set myRng1 = .Range("G" & FirstRow, "G" & LastRow)
Set myRng2 = .Range("H" & FirstRow, "H" & LastRow)
End With

myFormula = "sumproduct(" _
& "--(" & myRng1.Address(external:=True) _
& "="" Other "")*" _
& "--(" & myRng2.Address(external:=True) _
& "="" Shipper "")),(T43:V45)"

myVal = Application.Evaluate(myFormula)

End Sub

"Dave Peterson" wrote:

Option Explicit
Sub testme01()
Dim myRng1 As Range
Dim myRng2 As Range
Dim myVal As Double
Dim myFormula As String

With ActiveSheet
Set myRng1 = .Range("I341:I352")
Set myRng2 = .Range("H341").Resize(myRng1.Rows.Count)
'they have to be the same size, right?
End With

myFormula = "sumproduct(" _
& "--(" & myRng1.Address(external:=True) _
& "="" International "")," _
& "--(" & myRng2.Address(external:=True) _
& "="" Shipper ""))"

myVal = Application.Evaluate(myFormula)

MsgBox myVal
End Sub

If I understand what you meant...


Rafi wrote:

Dave,

I should have been clearer with my last question. I need help with using a
the variable myng in the context of the Sumproduct formula you gave me. If I
simply replace I314:i352 = ""International"" with myrng = ""International"" I
get an error.

Thanks again

"Rafi" wrote:

I would like to implemet the follwoing formula in VBA and would appreciate
any help

The intent is to summ the values in the range T341:T352 for those rows where
the value in colum I is International and the value in column H is Shipper

SUMPRODUCT((I341:I352=" International ")*(H341:H352=" Shipper "),(T341:T352))

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson