Can these functions work with arrays?

[agarwaldvk]

Hi Everybody

Can something similar to this be done in VBA?

We can write formulas such as the follows in a cell :-

=sumproduct(index($a1:$d500,,1)="test" * index($a1:$d500,,3)="thisNum")

This, as we know will give me the sum of the product of all the rows where the value in the range $a1:$a500 = "test" and the value in the range $c1:$c500 = "thisNum".

Can something similar be done using a 2 dimensional array where I can refer to a column of data in an array.

For example, if I am using a 500 row by 4 column array, can I use something similar to the above sumproduct function in VBA making use of the above array to get the sum of the product of the data in only those rows of the array where the data in the row in say the 3rd column of the array equals "test" and the data in the row in say the 4th column of the array equals "thisNum"?

Any suggestions?

I need to do using arrays because the underlying referred workbook is closed and I intend to work closed workbook. You may presume that the data from the referred closed has been fetched and loaded in a 2 dimensional array for processing.


Best regards



Deepak Agarwal

[Charlize]

Just loop the items in the array and if 3d and 4th column of active arrayitem equals your values add the value of the 1st column (if 1st column holds a number) to a variable otherwise proceed to next arrayitem.

[Bob Phillips]

You have to process it element by element

[vba]

For i = LBound(ary, 1) To UBound(ary, 1)
If ary(i, 1) = "test" Then
If ary(i, 3) = "thisnum" Then
nTotal = nTotal + 1
End If
End If
Next i
[/vba]

[Paul_Hossler]

Couple more ways -- I just used a little bit of data in my spreadsheet

I have used the .Evaluate method technique in a more general-purpose Sub by passing ranges, and using theie .Address to build the string

Paul


[VBA]
Sub aaa()
Dim s As String
Dim x As Double

'evaluated in the code
s = "=SUMPRODUCT($A2:$A10*$B2:$B10*($C2:$C10=""Test"")*($D2:$D10=""thisNum" "))"
x = Application.Evaluate(s)

MsgBox x


'or as a ws formula
Range("E5").FormulaArray = s

End Sub
[/VBA]