Nesting SUMIF

[RussellT]

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]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Bob Phillips]

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]

Thanks for you input Bob. I tried your formula but I get a RunTime Error
"Could not set Value Property. Type Mismatch"

 

[RussellT]

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]

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]

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

 

[RussellT]

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 & ")")

 

[RussellT]

Just one more question. How would the formula change if I want to count as
oppose to sum?

 

[Bob Phillips]

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

 

[Bob Phillips]

StatsForm.TextBox9 = .Evaluate("sumproduct(--(K2:K" & EndRow & "=""SFD""),"
& "--(at2:at" & EndRow & "=""" & myQTR & """))")

HTH

Bob

 

[Dave Peterson]

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.