Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
SUMPRODUCT | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Discussion (Misc queries) |