Sumif can't work with multiple criteria but you could check out the
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.
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.
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:
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:
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.
An alternative would be to use Excel's built-in filtering capabilities (autofilter or advanced filter), then grab the visible cells and sum those.
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.
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?
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:
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:
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.
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)