| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nesting SUMIF
I want to sum number in Column V if two conditions are met.
Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
You can't use AND like that in your =sumif() formula.
If you were using xl2007, you may be able to use =sumifs(). But there are alternatives. You could build a formula that would look like this on the plandata worksheet: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) (more on that later). Option Explicit Sub testme() Dim EndRow As Long Dim PlanDataSheet As Worksheet Dim FilterControlSheet As Worksheet Dim myFormula As String Dim Res As Variant 'could be an error! Dim myQTR As String Set PlanDataSheet = Worksheets("Plandata") Set FilterControlSheet = Worksheets("filtercontrol") 'I'm assuming that the value in this cell is really text--not a 'number that's formatted to look like 2009/4 myQTR = FilterControlSheet.Range("H2").Value With PlanDataSheet 'how ever you determined the end row EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row myFormula = "sumproduct(--(K2:K" & EndRow & "=""SFD"")," _ & "--(at2:at" & EndRow & "=""" & myQTR & """)," _ & "V2:V" & EndRow & ")" Res = .Evaluate(myFormula) If IsError(Res) Then MsgBox "It's an error" Else MsgBox Res End If End With End Sub Notice that the strings (like SFD and the 2009/4) in the formula have to be surrounded by double quotes--just like in the formula that would go in the cell. If you were comparing real numbers, the formula would look like: =sumproduct(--(K2:K24=1234),--(at2:at24=2009),V2:V24) without the double quotes. And notice that the .Evaluate that was used was with plandatasheet ... .evaluate(myformula) .... That means that all the addresses in that formula refer to plandatasheet. If I had used application.evaluate(), I'd have to be more careful. If I didn't include the sheetnames, then the unqualified addresses would refer to the activesheet (whichever that is!). Now more about that =sumproduct() formula: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html RussellT wrote: I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
Try
StatsForm.TextBox9 = Application.Evaluate("SUMPRODUCT(" & _ "(" & planDataSheet.Range("K2:K" & Endrow).Address(, , , True) & "=""""SFD"")*" & _ "(" & planDataSheet.Range("AT2:AT" & Endrow).Address(, , , True) & "=Quarter)*" & _ "(" & planDataSheet.Range("V2:V" & Endrow).Address(, , , True) & ")") HTH Bob "RussellT" wrote in message ... I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
Thanks for you input Bob. I tried your formula but I get a RunTime Error
"Could not set Value Property. Type Mismatch" "Bob Phillips" wrote: Try StatsForm.TextBox9 = Application.Evaluate("SUMPRODUCT(" & _ "(" & planDataSheet.Range("K2:K" & Endrow).Address(, , , True) & "=""""SFD"")*" & _ "(" & planDataSheet.Range("AT2:AT" & Endrow).Address(, , , True) & "=Quarter)*" & _ "(" & planDataSheet.Range("V2:V" & Endrow).Address(, , , True) & ")") HTH Bob "RussellT" wrote in message ... I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
Dave, thanks for your help. I'm not quite sure about the sumproduct thing.
I thought it worked like A1*B1 but for long list. "Dave Peterson" wrote: You can't use AND like that in your =sumif() formula. If you were using xl2007, you may be able to use =sumifs(). But there are alternatives. You could build a formula that would look like this on the plandata worksheet: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) (more on that later). Option Explicit Sub testme() Dim EndRow As Long Dim PlanDataSheet As Worksheet Dim FilterControlSheet As Worksheet Dim myFormula As String Dim Res As Variant 'could be an error! Dim myQTR As String Set PlanDataSheet = Worksheets("Plandata") Set FilterControlSheet = Worksheets("filtercontrol") 'I'm assuming that the value in this cell is really text--not a 'number that's formatted to look like 2009/4 myQTR = FilterControlSheet.Range("H2").Value With PlanDataSheet 'how ever you determined the end row EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row myFormula = "sumproduct(--(K2:K" & EndRow & "=""SFD"")," _ & "--(at2:at" & EndRow & "=""" & myQTR & """)," _ & "V2:V" & EndRow & ")" Res = .Evaluate(myFormula) If IsError(Res) Then MsgBox "It's an error" Else MsgBox Res End If End With End Sub Notice that the strings (like SFD and the 2009/4) in the formula have to be surrounded by double quotes--just like in the formula that would go in the cell. If you were comparing real numbers, the formula would look like: =sumproduct(--(K2:K24=1234),--(at2:at24=2009),V2:V24) without the double quotes. And notice that the .Evaluate that was used was with plandatasheet ... .evaluate(myformula) .... That means that all the addresses in that formula refer to plandatasheet. If I had used application.evaluate(), I'd have to be more careful. If I didn't include the sheetnames, then the unqualified addresses would refer to the activesheet (whichever that is!). Now more about that =sumproduct() formula: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html RussellT wrote: I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. -- Dave Peterson . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
It works like that with arrays, too.
K2:K24="sfd" will result in a 23 element array of true's and false's. --(true, false, ...) will convert it to 1's and 0's. Same with the other ranges/arrays. RussellT wrote: Dave, thanks for your help. I'm not quite sure about the sumproduct thing. I thought it worked like A1*B1 but for long list. "Dave Peterson" wrote: You can't use AND like that in your =sumif() formula. If you were using xl2007, you may be able to use =sumifs(). But there are alternatives. You could build a formula that would look like this on the plandata worksheet: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) (more on that later). Option Explicit Sub testme() Dim EndRow As Long Dim PlanDataSheet As Worksheet Dim FilterControlSheet As Worksheet Dim myFormula As String Dim Res As Variant 'could be an error! Dim myQTR As String Set PlanDataSheet = Worksheets("Plandata") Set FilterControlSheet = Worksheets("filtercontrol") 'I'm assuming that the value in this cell is really text--not a 'number that's formatted to look like 2009/4 myQTR = FilterControlSheet.Range("H2").Value With PlanDataSheet 'how ever you determined the end row EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row myFormula = "sumproduct(--(K2:K" & EndRow & "=""SFD"")," _ & "--(at2:at" & EndRow & "=""" & myQTR & """)," _ & "V2:V" & EndRow & ")" Res = .Evaluate(myFormula) If IsError(Res) Then MsgBox "It's an error" Else MsgBox Res End If End With End Sub Notice that the strings (like SFD and the 2009/4) in the formula have to be surrounded by double quotes--just like in the formula that would go in the cell. If you were comparing real numbers, the formula would look like: =sumproduct(--(K2:K24=1234),--(at2:at24=2009),V2:V24) without the double quotes. And notice that the .Evaluate that was used was with plandatasheet ... .evaluate(myformula) .... That means that all the addresses in that formula refer to plandatasheet. If I had used application.evaluate(), I'd have to be more careful. If I didn't include the sheetnames, then the unqualified addresses would refer to the activesheet (whichever that is!). Now more about that =sumproduct() formula: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html RussellT wrote: I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. -- Dave Peterson . -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
Bob, found your discussion on sumproduct at xldyamic.com and created the
following code for my formula. I substituted actual ranges as opposed to calcing endrows, but I still get aType Mismatch error. thanks Public Sub DoStats() Dim mStyle As String Dim mQtr As String Dim mFormula As String Dim mCount As Long Dim planDataSheet As Worksheet Dim filtersSheet As Worksheet Set filtersSheet = Sheets("Filters") Set planDataSheet = Sheets("PlanData") mStyle = "SFD" mQtr = "2009/4" mFormula = "SUMPRODUCT(plandataSheet.Range(K2:K18646=""" & mStyle & """)*(plandataSheet(AT2:AT18646=""" & mMonth & """))" mCount = Application.Evaluate(mFormula) MsgBox mCount End Sub "Bob Phillips" wrote: Try StatsForm.TextBox9 = Application.Evaluate("SUMPRODUCT(" & _ "(" & planDataSheet.Range("K2:K" & Endrow).Address(, , , True) & "=""""SFD"")*" & _ "(" & planDataSheet.Range("AT2:AT" & Endrow).Address(, , , True) & "=Quarter)*" & _ "(" & planDataSheet.Range("V2:V" & Endrow).Address(, , , True) & ")") HTH Bob "RussellT" wrote in message ... I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
Dave thanks every so much, I ended up with the following since I've got a ton
of textboxes to fill. StatsForm.TextBox9 = .Evaluate("sumproduct(--(K2:K" & EndRow & "=""SFD"")," & "--(at2:at" & EndRow & "=""" & myQTR & """)," & "V2:V" & EndRow & ")") "Dave Peterson" wrote: It works like that with arrays, too. K2:K24="sfd" will result in a 23 element array of true's and false's. --(true, false, ...) will convert it to 1's and 0's. Same with the other ranges/arrays. RussellT wrote: Dave, thanks for your help. I'm not quite sure about the sumproduct thing. I thought it worked like A1*B1 but for long list. "Dave Peterson" wrote: You can't use AND like that in your =sumif() formula. If you were using xl2007, you may be able to use =sumifs(). But there are alternatives. You could build a formula that would look like this on the plandata worksheet: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) (more on that later). Option Explicit Sub testme() Dim EndRow As Long Dim PlanDataSheet As Worksheet Dim FilterControlSheet As Worksheet Dim myFormula As String Dim Res As Variant 'could be an error! Dim myQTR As String Set PlanDataSheet = Worksheets("Plandata") Set FilterControlSheet = Worksheets("filtercontrol") 'I'm assuming that the value in this cell is really text--not a 'number that's formatted to look like 2009/4 myQTR = FilterControlSheet.Range("H2").Value With PlanDataSheet 'how ever you determined the end row EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row myFormula = "sumproduct(--(K2:K" & EndRow & "=""SFD"")," _ & "--(at2:at" & EndRow & "=""" & myQTR & """)," _ & "V2:V" & EndRow & ")" Res = .Evaluate(myFormula) If IsError(Res) Then MsgBox "It's an error" Else MsgBox Res End If End With End Sub Notice that the strings (like SFD and the 2009/4) in the formula have to be surrounded by double quotes--just like in the formula that would go in the cell. If you were comparing real numbers, the formula would look like: =sumproduct(--(K2:K24=1234),--(at2:at24=2009),V2:V24) without the double quotes. And notice that the .Evaluate that was used was with plandatasheet ... .evaluate(myformula) .... That means that all the addresses in that formula refer to plandatasheet. If I had used application.evaluate(), I'd have to be more careful. If I didn't include the sheetnames, then the unqualified addresses would refer to the activesheet (whichever that is!). Now more about that =sumproduct() formula: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html RussellT wrote: I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. -- Dave Peterson . -- Dave Peterson . |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
Just one more question. How would the formula change if I want to count as oppose to sum? "Dave Peterson" wrote: It works like that with arrays, too. K2:K24="sfd" will result in a 23 element array of true's and false's. --(true, false, ...) will convert it to 1's and 0's. Same with the other ranges/arrays. RussellT wrote: Dave, thanks for your help. I'm not quite sure about the sumproduct thing. I thought it worked like A1*B1 but for long list. "Dave Peterson" wrote: You can't use AND like that in your =sumif() formula. If you were using xl2007, you may be able to use =sumifs(). But there are alternatives. You could build a formula that would look like this on the plandata worksheet: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) (more on that later). Option Explicit Sub testme() Dim EndRow As Long Dim PlanDataSheet As Worksheet Dim FilterControlSheet As Worksheet Dim myFormula As String Dim Res As Variant 'could be an error! Dim myQTR As String Set PlanDataSheet = Worksheets("Plandata") Set FilterControlSheet = Worksheets("filtercontrol") 'I'm assuming that the value in this cell is really text--not a 'number that's formatted to look like 2009/4 myQTR = FilterControlSheet.Range("H2").Value With PlanDataSheet 'how ever you determined the end row EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row myFormula = "sumproduct(--(K2:K" & EndRow & "=""SFD"")," _ & "--(at2:at" & EndRow & "=""" & myQTR & """)," _ & "V2:V" & EndRow & ")" Res = .Evaluate(myFormula) If IsError(Res) Then MsgBox "It's an error" Else MsgBox Res End If End With End Sub Notice that the strings (like SFD and the 2009/4) in the formula have to be surrounded by double quotes--just like in the formula that would go in the cell. If you were comparing real numbers, the formula would look like: =sumproduct(--(K2:K24=1234),--(at2:at24=2009),V2:V24) without the double quotes. And notice that the .Evaluate that was used was with plandatasheet ... .evaluate(myformula) .... That means that all the addresses in that formula refer to plandatasheet. If I had used application.evaluate(), I'd have to be more careful. If I didn't include the sheetnames, then the unqualified addresses would refer to the activesheet (whichever that is!). Now more about that =sumproduct() formula: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html RussellT wrote: I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. -- Dave Peterson . -- Dave Peterson . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
This works, but I cannot see where you set mMonth
Public Sub DoStats() Dim mStyle As String Dim mQtr As String Dim mFormula As String Dim mCount As Long Dim mMonth As Long Dim planDataSheet As Worksheet Dim filtersSheet As Worksheet Set filtersSheet = Sheets("Filters") Set planDataSheet = Sheets("PlanData") mStyle = "SFD" mQtr = "2009/4" mFormula = "SUMPRODUCT(('" & planDataSheet.Name & "'!K2:K18646=""" & mStyle & """)*" & _ "('" & planDataSheet.Name & "'!AT2:AT18646=" & mMonth & "))" mCount = Application.Evaluate(mFormula) MsgBox mCount End Sub HTH Bob "RussellT" wrote in message ... Bob, found your discussion on sumproduct at xldyamic.com and created the following code for my formula. I substituted actual ranges as opposed to calcing endrows, but I still get aType Mismatch error. thanks Public Sub DoStats() Dim mStyle As String Dim mQtr As String Dim mFormula As String Dim mCount As Long Dim planDataSheet As Worksheet Dim filtersSheet As Worksheet Set filtersSheet = Sheets("Filters") Set planDataSheet = Sheets("PlanData") mStyle = "SFD" mQtr = "2009/4" mFormula = "SUMPRODUCT(plandataSheet.Range(K2:K18646=""" & mStyle & """)*(plandataSheet(AT2:AT18646=""" & mMonth & """))" mCount = Application.Evaluate(mFormula) MsgBox mCount End Sub "Bob Phillips" wrote: Try StatsForm.TextBox9 = Application.Evaluate("SUMPRODUCT(" & _ "(" & planDataSheet.Range("K2:K" & Endrow).Address(, , , True) & "=""""SFD"")*" & _ "(" & planDataSheet.Range("AT2:AT" & Endrow).Address(, , , True) & "=Quarter)*" & _ "(" & planDataSheet.Range("V2:V" & Endrow).Address(, , , True) & ")") HTH Bob "RussellT" wrote in message ... I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. . |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
StatsForm.TextBox9 = .Evaluate("sumproduct(--(K2:K" & EndRow & "=""SFD""),"
& "--(at2:at" & EndRow & "=""" & myQTR & """))") HTH Bob "RussellT" wrote in message ... Just one more question. How would the formula change if I want to count as oppose to sum? "Dave Peterson" wrote: It works like that with arrays, too. K2:K24="sfd" will result in a 23 element array of true's and false's. --(true, false, ...) will convert it to 1's and 0's. Same with the other ranges/arrays. RussellT wrote: Dave, thanks for your help. I'm not quite sure about the sumproduct thing. I thought it worked like A1*B1 but for long list. "Dave Peterson" wrote: You can't use AND like that in your =sumif() formula. If you were using xl2007, you may be able to use =sumifs(). But there are alternatives. You could build a formula that would look like this on the plandata worksheet: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) (more on that later). Option Explicit Sub testme() Dim EndRow As Long Dim PlanDataSheet As Worksheet Dim FilterControlSheet As Worksheet Dim myFormula As String Dim Res As Variant 'could be an error! Dim myQTR As String Set PlanDataSheet = Worksheets("Plandata") Set FilterControlSheet = Worksheets("filtercontrol") 'I'm assuming that the value in this cell is really text--not a 'number that's formatted to look like 2009/4 myQTR = FilterControlSheet.Range("H2").Value With PlanDataSheet 'how ever you determined the end row EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row myFormula = "sumproduct(--(K2:K" & EndRow & "=""SFD"")," _ & "--(at2:at" & EndRow & "=""" & myQTR & """)," _ & "V2:V" & EndRow & ")" Res = .Evaluate(myFormula) If IsError(Res) Then MsgBox "It's an error" Else MsgBox Res End If End With End Sub Notice that the strings (like SFD and the 2009/4) in the formula have to be surrounded by double quotes--just like in the formula that would go in the cell. If you were comparing real numbers, the formula would look like: =sumproduct(--(K2:K24=1234),--(at2:at24=2009),V2:V24) without the double quotes. And notice that the .Evaluate that was used was with plandatasheet ... .evaluate(myformula) .... That means that all the addresses in that formula refer to plandatasheet. If I had used application.evaluate(), I'd have to be more careful. If I didn't include the sheetnames, then the unqualified addresses would refer to the activesheet (whichever that is!). Now more about that =sumproduct() formula: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html RussellT wrote: I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. -- Dave Peterson . -- Dave Peterson . |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Nesting SUMIF
I'd still use that intermediate variable (I used Res).
That way if the result were an error, I could put what I wanted in the textbox--and the code wouldn't fail. RussellT wrote: Dave thanks every so much, I ended up with the following since I've got a ton of textboxes to fill. StatsForm.TextBox9 = .Evaluate("sumproduct(--(K2:K" & EndRow & "=""SFD"")," & "--(at2:at" & EndRow & "=""" & myQTR & """)," & "V2:V" & EndRow & ")") "Dave Peterson" wrote: It works like that with arrays, too. K2:K24="sfd" will result in a 23 element array of true's and false's. --(true, false, ...) will convert it to 1's and 0's. Same with the other ranges/arrays. RussellT wrote: Dave, thanks for your help. I'm not quite sure about the sumproduct thing. I thought it worked like A1*B1 but for long list. "Dave Peterson" wrote: You can't use AND like that in your =sumif() formula. If you were using xl2007, you may be able to use =sumifs(). But there are alternatives. You could build a formula that would look like this on the plandata worksheet: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) (more on that later). Option Explicit Sub testme() Dim EndRow As Long Dim PlanDataSheet As Worksheet Dim FilterControlSheet As Worksheet Dim myFormula As String Dim Res As Variant 'could be an error! Dim myQTR As String Set PlanDataSheet = Worksheets("Plandata") Set FilterControlSheet = Worksheets("filtercontrol") 'I'm assuming that the value in this cell is really text--not a 'number that's formatted to look like 2009/4 myQTR = FilterControlSheet.Range("H2").Value With PlanDataSheet 'how ever you determined the end row EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row myFormula = "sumproduct(--(K2:K" & EndRow & "=""SFD"")," _ & "--(at2:at" & EndRow & "=""" & myQTR & """)," _ & "V2:V" & EndRow & ")" Res = .Evaluate(myFormula) If IsError(Res) Then MsgBox "It's an error" Else MsgBox Res End If End With End Sub Notice that the strings (like SFD and the 2009/4) in the formula have to be surrounded by double quotes--just like in the formula that would go in the cell. If you were comparing real numbers, the formula would look like: =sumproduct(--(K2:K24=1234),--(at2:at24=2009),V2:V24) without the double quotes. And notice that the .Evaluate that was used was with plandatasheet ... .evaluate(myformula) .... That means that all the addresses in that formula refer to plandatasheet. If I had used application.evaluate(), I'd have to be more careful. If I didn't include the sheetnames, then the unqualified addresses would refer to the activesheet (whichever that is!). Now more about that =sumproduct() formula: =sumproduct(--(K2:K24="SFD"),--(at2:at24="2009/4"),V2:V24) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html RussellT wrote: I want to sum number in Column V if two conditions are met. Condition 1: Data in Column K = SFD Condition 2: Data in Column AT = value in cell H2 on another Sheet. Dim Quarter As String Quarter = filtercontrolSheet.Range("H2") contents of cell is 2009/4 I've tried the formula but get error message "Invalid number of arguments" Thanks for the assistance. StatsForm.TextBox9 = Application.SumIf(planDataSheet.Range("K2:K" & Endrow), "SFD" And planDataSheet.Range("AT2:AT" & Endrow), Quarter, planDataSheet.Range("V2:V" & Endrow)) I've also tried replacing the And joiner with * to no avail. -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| nesting sumif functions | Excel Worksheet Functions | |||
| SUMIF nesting an AND function | Excel Worksheet Functions | |||
| SUMIF nesting ??? | Excel Worksheet Functions | |||
| Nesting SUMIF | Excel Discussion (Misc queries) | |||
| Nesting of sumif formula. | Excel Worksheet Functions | |||
Linear Mode
