multiple criteria in sumif function - Xtreme Visual Basic Talk

01-10-2004, 02:11 AM

suaveali

Newcomer

Join Date: Jan 2004

Posts: 11

multiple criteria in sumif function

I am using an excel function worksheetfunction.sumif in a VB6 application. I want to sum values in a column of an excel sheet by using two sets of criteria.

for example,

column "C" should be summed only if the text in column "A" is "IP" and corresponding to this in column "B" the text is "Inforce".

Col A Col B Col C
IP Inforce 10
EP Inforce 20
IP Paidup 25
GB Inforce 35
IP Inforce 55

the code should return a sum of (10+55) = 65 ( first row and last row)

Any suggestions on how two sets of criteria and two sets of ranges can be given in a sumif function using VB6?

01-10-2004, 03:13 AM

italkid

Down...

Retired Moderator
* Expert *

Join Date: Dec 2002

Location: Belgium.

Posts: 6,731

Sumif can't work with multiple criteria but you could check out the
SumProduct worksheetfunction.
As a test you could first write a test formula straight in Excel and when
you got your formula working do the convertion to a VBA worksheetfunction.

01-11-2004, 08:01 AM

suaveali

Newcomer

Join Date: Jan 2004

Posts: 11

Actually i have written a code in VB running a For Next loop and having nested IF loops in the middle. The nested IF loops set the multiple criteria that I want the Sumif function to do. I get the correct resuts, but considering that I have 50,000 rows in the data, it takes a long time to run the loop. I am just trying to find a shorter way of summing a column using multiple criteria and thought sumif function could do it much faster.

If I run a sumif function only on a single criteria, i get an instant result, while using a For Next loop for this job takes more than half an hour to run on the complete data.

01-11-2004, 08:32 AM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

An Excel formula will definitely be much faster than looping. For the sample data in your question, this SUMPRODUCT formula would do what you want:
=SUMPRODUCT(($A$1:$A$5="IP")*($B$1:$B$5="Inforce")*$C$1:$C$5)

If you want to apply it through code, it would probably be easier to apply it with Evaluate rather than WorksheetFunction, for example like this:

Code:

intResult = Evaluate("=SUMPRODUCT(($A$1:$A$5=""IP"")*($B$1:$B$5=""Inforce"")*$C$1:$C$5)")

or if you want to specify the ranges as variables:

Code:

Dim rng1 As Range: Set rng1 = Range("A1:A5")
Dim rng2 As Range: Set rng2 = Range("B1:B5")
Dim rng3 As Range: Set rng3 = Range("C1:C5")
Dim intResult As Integer

intResult = Evaluate("=SUMPRODUCT((" & rng1.Address(True, True) & "=""IP"")*(" _
    & rng2.Address(True, True) & "=""Inforce"")*" & rng3.Address(True, True) & ")")

I'm not sure whether Evaluate is faster or slower than Worksheetfunction... if this is too slow, let us know and we can take another look.

01-11-2004, 08:36 AM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

An alternative would be to use Excel's built-in filtering capabilities (autofilter or advanced filter), then grab the visible cells and sum those.

Edit:
Another alternative would be to use pivot tables. This kind of thing is exactly what pivot tables are for. Pivot tables can be created through code, but they need to be in a worksheet, so this approach would involve changes to your Excel file.

01-11-2004, 12:55 PM

suaveali

Newcomer

Join Date: Jan 2004

Posts: 11

I have used the first formula you have suggested in Excel. it works fine.

However when I use the Evaluate formula in VB code, I am not sure why, but it returns a value of 0. The formula I am using is exactly the one you have suggested, both with and without naming the range as a variable.

Any suggestions on how to use this with the Worksheet function in VB code?

01-11-2004, 01:24 PM

Kluz

Sapience.Aquire

Super Moderator
* Expert *

Join Date: Oct 2003

Location: Lake Bluff, Ill., U.S.

Posts: 3,306

heraline's code works fine in Excel VBA, since you are using VB6 you will need to reference completly. Assuming you might use xlSheet as the object variable that references the worksheet your using and xlApp as the object variable you're calling Excel it would look like this:

Code:

intResult = xlApp.Evaluate("=SUMPRODUCT((xlSheet.Range("$A$1:$A$5")=""IP"")*(xlSheet.Range("$B$1:$B$5")=""Inforce"")*xlSheet.Range("$C$1:$C$5"))")

__________________
No the other right mouse click

01-11-2004, 02:40 PM

herilane

Unashamed geek

Retired Moderator
* Expert *

Join Date: Jul 2003

Location: London, England

Posts: 8,988

Sorry, my bad, I forgot you were doing this in VB6.

For the first formula, only Evaluate itself need to be referenced, since the range references are pure strings. However, the formula (in its current shape) will only give you the correct result if the data sheet is the active sheet. Otherwise it returns 0.

To fix that, you'll need to either activate the right sheet, or add sheet references to the formula (in Excel formula style, so to say). If the sheet is called Sheet1, it would look like this:

Code:

intResult = xlApp.Evaluate("=SUMPRODUCT((Sheet1!$A$1:$A$5=""IP"")*(Sheet1!$B$1:$B$5=""Inforce"")*Sheet1!$C$1:$C$5)")

The same principle applies if you want to use variables to specify the names of the worksheet and the ranges, but the code is a bit longer. Here, everything needs to be fully referenced.
I'm assuming xlWS is your worksheet object. To make it easier to debug, I've broken it down to two steps: building the formula, and then evaluating it.

Code:

Dim rng1 As Excel.Range: Set rng1 = xlWS.Range("A1:A5")
Dim rng2 As Excel.Range: Set rng2 = xlWS.Range("B1:B5")
Dim rng3 As Excel.Range: Set rng3 = xlWS.Range("C1:C5")

Dim strFormula As String
strFormula = "=SUMPRODUCT((" & xlWS.Name & "!" & rng1.Address(True, True) _
    & "=""IP"")*(" & xlWS.Name & "!" & rng2.Address(True, True) _
    & "=""Inforce"")*" & xlWS.Name & "!" & rng3.Address(True, True) & ")"

intResult = xlApp.Evaluate(strFormula)

Last edited by herilane; 01-11-2004 at 02:49 PM.

01-12-2004, 12:33 AM

suaveali

Newcomer

Join Date: Jan 2004

Posts: 11

I was using an incorrect reference. That's why the results were not correct.

Your above suggested code (with Evaluate) works and its fast. Thanks all of you for the help.