Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > multiple criteria in sumif function


Reply
 
Thread Tools Display Modes
  #1  
Old 01-10-2004, 02:11 AM
suaveali suaveali is offline
Newcomer
 
Join Date: Jan 2004
Posts: 11
Default 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?
Reply With Quote
  #2  
Old 01-10-2004, 03:13 AM
italkid's Avatar
italkid italkid is offline
Down...

Retired Moderator
* Expert *
 
Join Date: Dec 2002
Location: Belgium.
Posts: 6,731
Default

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.
Reply With Quote
  #3  
Old 01-11-2004, 08:01 AM
suaveali suaveali is offline
Newcomer
 
Join Date: Jan 2004
Posts: 11
Default

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.
Reply With Quote
  #4  
Old 01-11-2004, 08:32 AM
herilane's Avatar
herilane herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

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.
Reply With Quote
  #5  
Old 01-11-2004, 08:36 AM
herilane's Avatar
herilane herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

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.
Reply With Quote
  #6  
Old 01-11-2004, 12:55 PM
suaveali suaveali is offline
Newcomer
 
Join Date: Jan 2004
Posts: 11
Default

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?
Reply With Quote
  #7  
Old 01-11-2004, 01:24 PM
Kluz's Avatar
Kluz Kluz is offline
Sapience.Aquire

Super Moderator
* Expert *
 
Join Date: Oct 2003
Location: Lake Bluff, Ill., U.S.
Posts: 3,332
Default

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
Reply With Quote
  #8  
Old 01-11-2004, 02:40 PM
herilane's Avatar
herilane herilane is offline
Unashamed geek

Retired Moderator
* Expert *
 
Join Date: Jul 2003
Location: London, England
Posts: 8,988
Default

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.
Reply With Quote
  #9  
Old 01-12-2004, 12:33 AM
suaveali suaveali is offline
Newcomer
 
Join Date: Jan 2004
Posts: 11
Default

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.
Reply With Quote
Reply


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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
cd-key changer very_confused File I/O and Registry 2 06-23-2003 04:04 PM
need help! i'm a beginner geekool General 7 04-26-2003 12:44 AM
Shareware Registry Protection Technigue karachi999 General 2 01-21-2002 02:40 PM
MS Winsocket gibson General 3 07-20-2001 10:03 AM
link function to textboxes golash General 2 05-03-2001 11:57 AM

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->