Storing CountIfs arguments in an array?

Originally Posted by fomoz

i understand your point. doing Evaluate is not an option though (performance considerations). thanks!

Are you implying that:

Sub Example()
   Debug.Print [COUNTIFS(A1:A10,"x",B1:B10,"y")]
End Sub

is going to be noticeably slower than:

Sub Example()
    Debug.Print WorksheetFunction.CountIfs(Range("A1:A10"),"x",Range(B1:B10),"y")
End Sub

If the difference between the two in performance terms is a very real concern for you then I'm afraid you have "much bigger fish to fry" than worrying about the COUNTIFS element of your model.

edit:
If you're running mass COUNTIFS with huge precedent ranges and want to optimise then you should consider (if not already)

a) minimising precedent ranges as much as possible

b) using/automating intermediate Pivots and GETPIVOTDATA for results

option b) may seem bizarre but the performance gains can be immense