Countif for VBA array without a loop

[sheetspread]

These two posts suggest it can't be done:

http://www.mrexcel.com/forum/excel-questions/672885-visual-basic-applications-countif-array.html

http://www.mrexcel.com/forum/excel-...lications]-count-number-occurances-array.html


But shg said:

You can use worksheet functions in VBA, and could use WorksheetFunction.SumProduct for this. Some functions (CountIf, Rank, some others) work only with ranges (and IntelliSense will tell you), but SumProduct is happy with arrays.

I tried using evaluate sumproduct with a myarray (as variant) and couldn't get it to work.

Example: myarray = array("First","Second","Second","Third","Fourth")

then what would the countif(myarray,"second") syntax be? (the result should equal 2 counts)


The only other method I can think of is printing the array to a worksheet range (which doesn't require a loop), counting off that, then deleting.

 

[sheetspread]

Try:

Excel 2010
	A	B
1	Seventh
2	Second
3	Second
4	First
5	Second
6	Second
7	Second
8	First
9	Second
10	Second
11	Third
12	Second
13	Second
14	Fifth
15	Second
Sheet6

Sub a()
Range("B1").Value = Application.CountIf(Range("a1:a15"), "Second")
End Sub

Excel 2010
	A	B
1	Seventh	10
2	Second
3	Second
4	First
5	Second
6	Second
7	Second
8	First
9	Second
10	Second
11	Third
12	Second
13	Second
14	Fifth
15	Second
Sheet6