
01102004, 03:11 AM

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?


01102004, 04:13 AM


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.


01112004, 09:01 AM

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.


01112004, 09:32 AM


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.


01112004, 09:36 AM


Unashamed geek
Retired Moderator * Expert *


Join Date: Jul 2003
Location: London, England
Posts: 8,988


An alternative would be to use Excel's builtin 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.


01112004, 01:55 PM

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?


01112004, 02:24 PM


Sapience.Aquire
Super Moderator * Expert *


Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,340


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

01112004, 03:40 PM


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; 01112004 at 03:49 PM.

01122004, 01:33 AM

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.


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)


Thread Tools 

Display Modes 
Linear Mode

Posting Rules

You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off





