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

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

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

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

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

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

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

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

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

=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

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

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

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

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

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 ***

=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