SumIf using two criteria

[SA3214]

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

 

[Guest]

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

 

[Bob Phillips]

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

--

HTH

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

 

[Paul Black]

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:A>0,IF(B:B>0,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

 

[Tom Ogilvy]

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

 

[SA3214]

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 ?

 

[Guest]

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]

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

 

[Guest]

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

 

[Bob Phillips]

Easier to use Evaluate for complex formulae.

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

--

HTH

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

 

[Bob Phillips]

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

 

[Tom Ogilvy]

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.

 

[SA3214]

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]

I think that was why Jim was suggesting using SUMPRODUCT?

 

[SA3214]

.... 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)=TextBox4),D110))")

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

 

[Guest]

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

 

[Guest]

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]

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


--

HTH

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

 

[Bob Phillips]

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.

 

[SA3214]

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 & "),D110))")
End With

End With
End Sub