Hi Jim,

In VBA, you cannot use worksheet ranges, you need to specify the object (I
know that you know, but the OP may not). SO at least you need


txtMyTextBox.value =
application.SUMPRODUCT(--(Range("A1:A1000")="val1"),--(Range("B1:B1000")="va
l2"),Range("C1:C1000"))

but this just throws a type mismatch. VBA doesn't like the -- operator, and
even using * doesn't fix it.

I have found only evaluate works with SUMPRODUCT in VBA, and that works okay
with your original as well.

Regards

Bob


"Jim Thomlinson" wrote in message
...
Here you want to use the sumproduct that Bob or Tom indicated.

txtMyTextBox.value =
application.SUMPRODUCT(--(A1:A1000="val1"),--(B1:B1000="val2"),C1:C1000)

I have not tested it but that should be it...

HTH

"SA3214 @Eclipse.co.uk" wrote:

Thanks to all for your input.

Now I need to do the same thing using vba to display the total in a
textbox

Can I use a WorksheetFunction to achieve this ?




"Jim Thomlinson" wrote in
message
...
Very Possible to do. You want an array formula. The big trick here is
the
and. In an array formula and is represented by * and or is represented
by
^.
So the formula you want is something like this...

=SUM(IF(((A1:A10)="This")*((B1:B10)="That"), C1:C10))

Note that to enter an ary formula you need to hit Shift+Ctrl+Enter
instead
of just enter. The formula will be displayed with curly{} braces
around it
when you do...

HTH

"SA3214 @Eclipse.co.uk" wrote:

Is it possible to sum a range of cells based on two criteria
eg Sum of cells in Column(C) ... if Column(A)=string1 and Column(B) =
string2

Hope you can understand that


Regards and TIA