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
.