Convert a Countifs with ?array? sum to VBA

[yakfan]

I have the following set of test data:

	A	B	C	D	E	F	G	H	I
1	ID	svc from	svc to	svc 1 & 2count		ID	svc from	svc to	svc code
2	1	1/1/2010	1/31/2010	3		1	1/1/2010	1/31/2010	1
3	1	2/1/2010	2/28/2010	3		1	1/1/2010	1/31/2010	3
4	2	2/1/2010	2/28/2010	3		1	1/1/2010	1/31/2010	2
5	2	3/1/2010	3/31/2010	2		1	1/1/2010	1/31/2010	1
6	2	4/1/2010	4/30/2010	1		1	2/1/2010	2/28/2010	5
7	3	4/1/2010	4/30/2010	0		1	2/1/2010	2/28/2010	1
8	3	5/1/2010	5/31/2010	3		1	2/1/2010	2/28/2010	2
9	3	6/1/2010	6/30/2010	1		1	2/1/2010	2/28/2010	1
10	4	7/1/2010	7/31/2010	1		1	2/1/2010	2/28/2010	4
11	5	7/1/2010	7/31/2010	2		2	2/1/2010	2/28/2010	1
12						2	2/1/2010	2/28/2010	5
13						2	2/1/2010	2/28/2010	1
14						2	2/1/2010	2/28/2010	2
15						2	2/1/2010	2/28/2010	4
16						2	2/1/2010	2/28/2010	4
17						2	3/1/2010	3/31/2010	2
18						2	3/1/2010	3/31/2010	3
19						2	3/1/2010	3/31/2010	1
20						2	3/1/2010	3/31/2010	4
21						2	4/1/2010	4/30/2010	2
22						3	4/1/2010	4/30/2010	3
23						3	4/1/2010	4/30/2010	4
24						3	4/1/2010	4/30/2010	5
25						3	5/1/2010	5/31/2010	2
26						3	5/1/2010	5/31/2010	2
27						3	5/1/2010	5/31/2010	2
28						3	6/1/2010	6/30/2010	2
29						3	6/1/2010	6/30/2010	4
30						4	7/1/2010	7/31/2010	1
31						4	7/1/2010	7/31/2010	5
32						4	7/1/2010	7/31/2010	4
33						5	7/1/2010	7/31/2010	2
34						5	7/1/2010	7/31/2010	4
35						5	7/1/2010	7/31/2010	4
36						5	7/1/2010	7/31/2010	4
37						5	7/1/2010	7/31/2010	1

<tbody>
</tbody>

Cell D2 =SUM(COUNTIFS($F$2:$F$37,A2,$G$2:$G$37,B2,$H$2:$H$37,C2,$I$2:$I$37,{1,2}))

Lets just assume that I have a formula =SUM(Countifs(I2:I37,{1,2})), how would I write that to VBA?
I can probably handle the rest. I just have no clue how to use arrays this way in VBA or how to begin looking to learn.

 

[sijpie]

in VBA if you want to write a formula to a cell, you use

cell(2,4).formula="Sum(blah,blah)"

for arrayformulas you use

cell(2,4).formulaArray="Sum(blah,blah)"

 

[sijpie]

If you need to fill out the whole column D with the formula it is quicker to use RC references, becaus you can then set the range to the formula in one sweep.

 

[yakfan]

Thanks. But I'm sorry, I forgot to mention that I do not want to input a formula into the cell, but be able to evaluate the expression and determine a value using VBA. This value/variable will then be evaluated to return a simple Yes or No.

Additional details you may not need to know:

My final product will be to put this within a set of nested if statements to evaluate different aspects of the data to return a Yes or No. (Would not want it to say "Yes", "Yes", "No", [long formula], "Yes"). Also, I want to be able to accommodate the user specifying what "svc codes" out of hundreds they want to count.

I'm not sure if I can actually use RC referencing. In the end, there are a bunch of dynamically changing factors I would be accommodating for. The "svc from" column may or may not be located in B. I'm using Match() to determine where each column is and will be referencing in that manner, so my column references would be "2" rather than "B". The final output would also be displayed on the "LastColumn +1". All this probably makes it significantly more challenging to shove in a formula into the cell and dealing with additional referencing factors.

 

[sijpie]

Ah, understand.

Well you can Dim a variant and then load the complete table into the variant. This will give you an array, two dimensional, one withthe rows, the other with the columns.

Now you can do your calculations on the elements in the array, which is a lot quicker than doing it on the sheet. You can even use Application.WorksheetFunction to access all functions and work them on the array.

Or you can loop through all the elements of a row or column in the array and test the values and sum them. Because it doesn't read off the sheet it is very fast.

There will undoubtedly be some tutorials on working with arrays on the web. I am using an excellent book: VBA and Macros for Microsoft Excel, by Jelen and Syrstad.

 

[yakfan]

I was hoping it would be closely related to and be nearly as simple as the formula. Something along the lines of:

Dim myArray As Variant

myArray = Array(1, 2, 3)

Range("D2").Value = Application.WorksheetFunction.ArraySum( _
    Application.WorksheetFunction.countifs(Range("I2:I37"), myArray))

I bought the book you referenced on kindle and after reading the array chapter, I guess that wouldn't be nearly as simple as the formula looked and I'd have to use a loop to individually call each data set in the array to sum it up.
Thanks for the recommendation though, this would help me a lot.

I'm pretty sure that I can do this now without the use of an array. However, lets say I want to make it run faster and store the entire data table into an array as you suggested. How would you manipulate ranges inside the table after loading it into the array? Let's say I load the table from F2:I37 into an array.

Dim myArray As Variant

myArray = Range("F2:I37")

How would I emulate =countif(I1:I37,1) using the 2D array? I can load array data individually, but have no idea how to use it as a range.

 

[sijpie]

here is a small example. You have to take care with arrays, because they are sneaky in that rows and columns seem to be reversed. So that is why i use the transpose to load the rang in the array. by the way
[B2:B11] is a short form for
Range("B2:B11")

Sub testar()    
    Dim arA As Variant
    
    arA = Application.WorksheetFunction.Transpose([B2:B11])
    
    MsgBox Application.WorksheetFunction.Sum(arA)


End Sub

What is always helpful working with arrays is to add the array to the 'Watch' window in the macro editor. Then you can see how the data are sitting in the array

 

[yakfan]

That makes it into a single dimension array.

But how would you selectively pick out a single column of data in the array if you had loaded an entire table, say A1:B11? I can't separately make multiple single dimension arrays and expect them to work together for setting multiple conditions inside a countifs() function, can I?

 

[sijpie]

Well you probably can with careful programming, but I think it may be easier to do the countif and sumif in a self written sub that you call from your main program.

See also CPearson's website, for instance:
Passing And Returning Arrays With Functions
where he has an example of sumMulti

CPearson.com Topic Index
VBA Arrays