#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 834
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 38
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 834
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 834
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
nesting sumif functions Macil Excel Worksheet Functions 5 October 15th 08 08:41 PM
SUMIF nesting an AND function kraymond Excel Worksheet Functions 6 September 11th 08 07:16 AM
SUMIF nesting ??? Dave Excel Worksheet Functions 3 June 9th 08 05:18 PM
Nesting SUMIF 360Kid Excel Discussion (Misc queries) 3 June 1st 06 06:08 PM
Nesting of sumif formula. ramana Excel Worksheet Functions 2 January 2nd 06 12:00 PM


All times are GMT +1. The time now is 01:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"