suaveali

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?

italkid

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.

suaveali

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.

herilane

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:
or if you want to specify the ranges as variables:
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.

herilane

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.

suaveali

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?

Kluz

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: