SumIf using two criteria
 

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

SumIf using two criteria
 

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

SumIf using two criteria
 

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
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

SumIf using two criteria
 

Hi SA3214,

Not Quite Sure what you want, But you could give this a go. If there is
ANY Number in Column A AND Column B then Sum Column C :-

=IF(A:A0,IF(B:B0,SUM(C:C),0),0)

HTH
All the Best.
Paul



SumIf using two criteria
From: SA3214" @Eclipse.co.uk

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


*** Sent via Developersdex http://www.developersdex.com ***

SumIf using two criteria
 

=sumproduct(-(A1:A100="string1"),-(B1:B100="string2"),C1:C100)

--
Regards,
Tom Ogilvy

"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
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

SumIf using two criteria
 

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

SumIf using two criteria
 

Out of curiosity would a sumproduct be more effiecient than the array formula
equivalent. I tend to do the array formulas purely out of habit. Is it worth
switching to sumproduct or does it make any difference?

"Bob Phillips" wrote:

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
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

SumIf using two criteria
 

Yes, it is quicker. Frank Kabel and I did some timings on it once, I will
try and dig them out. Probably more important, is not having to do
Ctrl-Shift-Enter :-)

Regards

Bob

"Jim Thomlinson" wrote in message
...
Out of curiosity would a sumproduct be more effiecient than the array
formula
equivalent. I tend to do the array formulas purely out of habit. Is it
worth
switching to sumproduct or does it make any difference?

"Bob Phillips" wrote:

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
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

SumIf using two criteria
 

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

SumIf using two criteria
 

Easier to use Evaluate for complex formulae.

Debug.Print Evaluate("SUM(IF(((A1:A10)=""This"")*((B1:B10)=""T hat""),
C1:C10))")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
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

SumIf using two criteria
 

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

SumIf using two criteria
 

Just to Add:
You can not do any type of Arrayformula using worksheetfunction. You have
to use evaluate. If it has to be Arrayentered in a worksheet, then you need
to use evaluate.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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

SumIf using two criteria
 

Thank you for your input ... I was, as you rightly pointed out, getting the
type mismatch error
Now I am using Evaluate it works correctly

Regards to you all and many thanks for your help
.................

"Bob Phillips" wrote in message
...
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

SumIf using two criteria
 

I think that was why Jim was suggesting using SUMPRODUCT?


"Tom Ogilvy" wrote in message
...
Just to Add:
You can not do any type of Arrayformula using worksheetfunction. You
have
to use evaluate. If it has to be Arrayentered in a worksheet, then you
need
to use evaluate.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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

SumIf using two criteria ... Ooops I spoke too soon
 

.... I am getting a type mismatch error again

I substituted ComboBox2 and Textbox4 for the criteria as follows:

TextBox11 =
Evaluate("SUM(IF(((A1:A10)=Combobox2)*((B1:B10)=Te xtBox4),D1:D10))")

and it falls over again .... any suggestions please



"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
Thank you for your input ... I was, as you rightly pointed out, getting
the type mismatch error
Now I am using Evaluate it works correctly

Regards to you all and many thanks for your help
................

"Bob Phillips" wrote in message
...
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

SumIf using two criteria
 

I got caught nappin... You don't miss much. I cut and pasted without even
thinking... Thanks Bob... :)

"Bob Phillips" wrote:

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

SumIf using two criteria
 

There is not much that you don't know.... I figured if I asked... But I only
half expected an answer... Your input is invaluable. I shall start using
sumproduct... If you are ever in Vancouver Canada I will buy you a beer....

"Bob Phillips" wrote:

Yes, it is quicker. Frank Kabel and I did some timings on it once, I will
try and dig them out. Probably more important, is not having to do
Ctrl-Shift-Enter :-)

Regards

Bob

"Jim Thomlinson" wrote in message
...
Out of curiosity would a sumproduct be more effiecient than the array
formula
equivalent. I tend to do the array formulas purely out of habit. Is it
worth
switching to sumproduct or does it make any difference?

"Bob Phillips" wrote:

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
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

SumIf using two criteria ... Ooops I spoke too soon
 

TextBox11 =
Evaluate("SUM(IF(((A1:A10)=" & Combobox2.Value & ")*((B1:B10)=" &
TextBox4.Text & "),D1:D10))")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
.... I am getting a type mismatch error again

I substituted ComboBox2 and Textbox4 for the criteria as follows:

TextBox11 =
Evaluate("SUM(IF(((A1:A10)=Combobox2)*((B1:B10)=Te xtBox4),D1:D10))")

and it falls over again .... any suggestions please



"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
Thank you for your input ... I was, as you rightly pointed out, getting
the type mismatch error
Now I am using Evaluate it works correctly

Regards to you all and many thanks for your help
................

"Bob Phillips" wrote in message
...
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

SumIf using two criteria
 

Funnily enough my wife is talking about a trip next year, to include a
cruise to Alaska. SO if it happens, I will drop in and crack one with you.

I went to Vancouver many years ago, liked it a lot. They had re-developed
the Gaslight area (I think that was what it was called), which was a bit of
a shame, I would have liked to have seen it before the change :-)

Regards

Bob

PS I found my timings data, if you send me your email address, I will
forward you a copy of the workbook.


"Jim Thomlinson" wrote in message
...
There is not much that you don't know.... I figured if I asked... But I
only
half expected an answer... Your input is invaluable. I shall start using
sumproduct... If you are ever in Vancouver Canada I will buy you a
beer....

"Bob Phillips" wrote:

Yes, it is quicker. Frank Kabel and I did some timings on it once, I
will
try and dig them out. Probably more important, is not having to do
Ctrl-Shift-Enter :-)

Regards

Bob

"Jim Thomlinson" wrote in
message
...
Out of curiosity would a sumproduct be more effiecient than the array
formula
equivalent. I tend to do the array formulas purely out of habit. Is it
worth
switching to sumproduct or does it make any difference?

"Bob Phillips" wrote:

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
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

SumIf using two criteria ... Ooops I spoke too soon
 

I'm still getting an error as follows:

Runtime error '-2147352571 (80020005)
Could not set the value property. Type mismatch

I have two sheets in the workbook Invoices and Payments
ComboBox2 contains a supplier name selected from a list of Invoices
(recorded on sheet 1)
Textbox4 contains an Invoice Number which is selected by using a textbox
with an associated spinner
The spinup and spindown events cycle through the invoices for the supplier
Each spinner event is supposed to display the sum of all payments made
against the invoice (recorded on sheet 2)
see code below

In desperation I have formated the cells containing the invoice number as
both Text and Number on both sheets without having any effect on the error

Any help would be appreciated
............................................

Private Sub SpinButton1_SpinDown()
With Sheets("Invoices")
Set C = .Range("a:a").FindNext(C)
If C.Address = FirstAddress Then
Set C = .Range("a:a").FindPrevious(C)
LastAddress = C.Address
MsgBox "Last Invoice for " & ComboBox2
End If
TextBox4 = .Cells(C.Row, 3)
TextBox5 = .Cells(C.Row, 4)

With Sheets("Payments")
'next line gives error
TextBox11 = Evaluate("SUM(IF(((A1:A10)=" & ComboBox2.Value &
")*((B1:B10)=" & TextBox4.Text & "),D1:D10))")
End With

End With
End Sub

"Bob Phillips" wrote in message
...
TextBox11 =
Evaluate("SUM(IF(((A1:A10)=" & Combobox2.Value & ")*((B1:B10)=" &
TextBox4.Text & "),D1:D10))")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
.... I am getting a type mismatch error again

I substituted ComboBox2 and Textbox4 for the criteria as follows:

TextBox11 =
Evaluate("SUM(IF(((A1:A10)=Combobox2)*((B1:B10)=Te xtBox4),D1:D10))")

and it falls over again .... any suggestions please



"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
Thank you for your input ... I was, as you rightly pointed out, getting
the type mismatch error
Now I am using Evaluate it works correctly

Regards to you all and many thanks for your help
................

"Bob Phillips" wrote in message
...
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

SumIf using two criteria ... Ooops I spoke too soon
 

It is probably caused because you are comparing strings, and I didn't allow
for this. If both the A and B columns are text, use

TextBox11 = Evaluate("SUM(IF(((A1:A10)=""" & ComboBox1.Value & _
""")*((B1:B10)=""" & Textbox4.Text & """),D1:D10))")

If only column A is text, and B is a number, use

TextBox11 = Evaluate("SUM(IF(((A1:A10)=""" & ComboBox1.Value & _
""")*((B1:B10)=" & CDbl(Textbox4.Text) & "),D1:D10))")

Hope that this sorts it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
I'm still getting an error as follows:

Runtime error '-2147352571 (80020005)
Could not set the value property. Type mismatch

I have two sheets in the workbook Invoices and Payments
ComboBox2 contains a supplier name selected from a list of Invoices
(recorded on sheet 1)
Textbox4 contains an Invoice Number which is selected by using a textbox
with an associated spinner
The spinup and spindown events cycle through the invoices for the supplier
Each spinner event is supposed to display the sum of all payments made
against the invoice (recorded on sheet 2)
see code below

In desperation I have formated the cells containing the invoice number as
both Text and Number on both sheets without having any effect on the error

Any help would be appreciated
...........................................

Private Sub SpinButton1_SpinDown()
With Sheets("Invoices")
Set C = .Range("a:a").FindNext(C)
If C.Address = FirstAddress Then
Set C = .Range("a:a").FindPrevious(C)
LastAddress = C.Address
MsgBox "Last Invoice for " & ComboBox2
End If
TextBox4 = .Cells(C.Row, 3)
TextBox5 = .Cells(C.Row, 4)

With Sheets("Payments")
'next line gives error
TextBox11 = Evaluate("SUM(IF(((A1:A10)=" & ComboBox2.Value &
")*((B1:B10)=" & TextBox4.Text & "),D1:D10))")
End With

End With
End Sub

"Bob Phillips" wrote in message
...
TextBox11 =
Evaluate("SUM(IF(((A1:A10)=" & Combobox2.Value & ")*((B1:B10)=" &
TextBox4.Text & "),D1:D10))")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
.... I am getting a type mismatch error again

I substituted ComboBox2 and Textbox4 for the criteria as follows:

TextBox11 =
Evaluate("SUM(IF(((A1:A10)=Combobox2)*((B1:B10)=Te xtBox4),D1:D10))")

and it falls over again .... any suggestions please



"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
Thank you for your input ... I was, as you rightly pointed out,
getting
the type mismatch error
Now I am using Evaluate it works correctly

Regards to you all and many thanks for your help
................

"Bob Phillips" wrote in message
...
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

SumIf using two criteria
 

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

I was expanding that statement to include all array entered formulas.

Application.SumProduct used two multiply, then sum the product of two
ranges as designed works fine - but that is not an array formula in the
sense of above.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...
I think that was why Jim was suggesting using SUMPRODUCT?


"Tom Ogilvy" wrote in message
...
Just to Add:
You can not do any type of Arrayformula using worksheetfunction. You
have
to use evaluate. If it has to be Arrayentered in a worksheet, then you
need
to use evaluate.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
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

SumIf using two criteria ... Ooops I spoke too soon
 

Many many thanks - it works fine now

My apologies for the late acknowledgement ... I cut and pasted you piece of
code and it took me a while to figure out why it didn't work
(I am using ComboBox2 not ComboBox1)
I also moved the code from the spin events to the TextBox4.Change event.

I, and many thousands of others, owe you and your colleagues in this group a
great deal for the amount of time and effort you save us in solving these
vba problems. So much so that it often very tempting to ask before spending
even attempting to solve them for ourselves.

Heartfelt thanks and many regards

Jim Burton
(SA3214)



"Bob Phillips" wrote in message
...
It is probably caused because you are comparing strings, and I didn't
allow
for this. If both the A and B columns are text, use

TextBox11 = Evaluate("SUM(IF(((A1:A10)=""" & ComboBox1.Value & _
""")*((B1:B10)=""" & Textbox4.Text & """),D1:D10))")

If only column A is text, and B is a number, use

TextBox11 = Evaluate("SUM(IF(((A1:A10)=""" & ComboBox1.Value & _
""")*((B1:B10)=" & CDbl(Textbox4.Text) & "),D1:D10))")

Hope that this sorts it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
I'm still getting an error as follows:

Runtime error '-2147352571 (80020005)
Could not set the value property. Type mismatch

I have two sheets in the workbook Invoices and Payments
ComboBox2 contains a supplier name selected from a list of Invoices
(recorded on sheet 1)
Textbox4 contains an Invoice Number which is selected by using a textbox
with an associated spinner
The spinup and spindown events cycle through the invoices for the
supplier
Each spinner event is supposed to display the sum of all payments made
against the invoice (recorded on sheet 2)
see code below

In desperation I have formated the cells containing the invoice number as
both Text and Number on both sheets without having any effect on the
error

Any help would be appreciated
...........................................

Private Sub SpinButton1_SpinDown()
With Sheets("Invoices")
Set C = .Range("a:a").FindNext(C)
If C.Address = FirstAddress Then
Set C = .Range("a:a").FindPrevious(C)
LastAddress = C.Address
MsgBox "Last Invoice for " & ComboBox2
End If
TextBox4 = .Cells(C.Row, 3)
TextBox5 = .Cells(C.Row, 4)

With Sheets("Payments")
'next line gives error
TextBox11 = Evaluate("SUM(IF(((A1:A10)=" & ComboBox2.Value &
")*((B1:B10)=" & TextBox4.Text & "),D1:D10))")
End With

End With
End Sub

"Bob Phillips" wrote in message
...
TextBox11 =
Evaluate("SUM(IF(((A1:A10)=" & Combobox2.Value & ")*((B1:B10)=" &
TextBox4.Text & "),D1:D10))")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
.... I am getting a type mismatch error again

I substituted ComboBox2 and Textbox4 for the criteria as follows:

TextBox11 =
Evaluate("SUM(IF(((A1:A10)=Combobox2)*((B1:B10)=Te xtBox4),D1:D10))")

and it falls over again .... any suggestions please



"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
Thank you for your input ... I was, as you rightly pointed out,
getting
the type mismatch error
Now I am using Evaluate it works correctly

Regards to you all and many thanks for your help
................

"Bob Phillips" wrote in message
...
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

SumIf using two criteria ... Ooops I spoke too soon
 

Thanks Jim, it is appreciated.

Regards

Bob


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
Many many thanks - it works fine now

My apologies for the late acknowledgement ... I cut and pasted you piece
of
code and it took me a while to figure out why it didn't work
(I am using ComboBox2 not ComboBox1)
I also moved the code from the spin events to the TextBox4.Change event.

I, and many thousands of others, owe you and your colleagues in this group
a
great deal for the amount of time and effort you save us in solving these
vba problems. So much so that it often very tempting to ask before
spending
even attempting to solve them for ourselves.

Heartfelt thanks and many regards

Jim Burton
(SA3214)



"Bob Phillips" wrote in message
...
It is probably caused because you are comparing strings, and I didn't
allow
for this. If both the A and B columns are text, use

TextBox11 = Evaluate("SUM(IF(((A1:A10)=""" & ComboBox1.Value & _
""")*((B1:B10)=""" & Textbox4.Text & """),D1:D10))")

If only column A is text, and B is a number, use

TextBox11 = Evaluate("SUM(IF(((A1:A10)=""" & ComboBox1.Value & _
""")*((B1:B10)=" & CDbl(Textbox4.Text) & "),D1:D10))")

Hope that this sorts it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
I'm still getting an error as follows:

Runtime error '-2147352571 (80020005)
Could not set the value property. Type mismatch

I have two sheets in the workbook Invoices and Payments
ComboBox2 contains a supplier name selected from a list of Invoices
(recorded on sheet 1)
Textbox4 contains an Invoice Number which is selected by using a
textbox
with an associated spinner
The spinup and spindown events cycle through the invoices for the
supplier
Each spinner event is supposed to display the sum of all payments made
against the invoice (recorded on sheet 2)
see code below

In desperation I have formated the cells containing the invoice number
as
both Text and Number on both sheets without having any effect on the
error

Any help would be appreciated
...........................................

Private Sub SpinButton1_SpinDown()
With Sheets("Invoices")
Set C = .Range("a:a").FindNext(C)
If C.Address = FirstAddress Then
Set C = .Range("a:a").FindPrevious(C)
LastAddress = C.Address
MsgBox "Last Invoice for " & ComboBox2
End If
TextBox4 = .Cells(C.Row, 3)
TextBox5 = .Cells(C.Row, 4)

With Sheets("Payments")
'next line gives error
TextBox11 = Evaluate("SUM(IF(((A1:A10)=" & ComboBox2.Value
&
")*((B1:B10)=" & TextBox4.Text & "),D1:D10))")
End With

End With
End Sub

"Bob Phillips" wrote in message
...
TextBox11 =
Evaluate("SUM(IF(((A1:A10)=" & Combobox2.Value & ")*((B1:B10)=" &
TextBox4.Text & "),D1:D10))")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
.... I am getting a type mismatch error again

I substituted ComboBox2 and Textbox4 for the criteria as follows:

TextBox11 =
Evaluate("SUM(IF(((A1:A10)=Combobox2)*((B1:B10)=Te xtBox4),D1:D10))")

and it falls over again .... any suggestions please



"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
Thank you for your input ... I was, as you rightly pointed out,
getting
the type mismatch error
Now I am using Evaluate it works correctly

Regards to you all and many thanks for your help
................

"Bob Phillips" wrote in
message
...
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