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