Many thanks, Frank. That works -- your advice is a great help -- I appreciate it.

"Frank Kabel" wrote:

Hi
try
res = Evaluate("COUNT(IF(("& Range1.address &""& var1 &")*("&
Range1.address &"<="& var2 &"),"& Range2.address &"))")

--
Regards
Frank Kabel
Frankfurt, Germany


Scott P wrote:
Thanks very much, Tom -- this is quite helpful. One additional
question -- is there a way to substitute range variables for the cell
references in your below example? I tried this:

res = Evaluate("COUNT(IF(("& Range1 &""& var1 &")*("& Range1 &"<="&
var2 &"),"& Range2 &"))")

but it did not work. What am I doing wrong?

"Tom Ogilvy" wrote:

You don't Scott. Sumproduct doesn't work as an array formula in
VBA. Array formulas are not supported in VBA. You can use Evaluate
as already instructed, but if you do, there is no reason not to use
your original formula

res = Evaluate("COUNT(IF((B2:D120)*(B2:D12<=2),B2:D12)) ")


If you want to make 0 and 2 as variables

var1 = 0
var2 = 2

res = Evaluate("COUNT(IF((B2:D12"& var1 & _
")*(B2:D12<=" & var2 & "),B2:D12))")


so the argument to evaluate is a string value that would be a
legitimate formula if entered in a cell. You can use concatenation
to build that string and concatenate your variables into it. To use
SUMPRODUCT, you would use the same princple as above - built a
legitimate formula string.


--
Regards,
Tom Ogilvy

"Scott P" wrote in message
...
Thanks for the help, Pascal and Frank. In terms of VBA support for
that
syntax, the following statement works for me in VBA: Result =
Application.SumProduct(Array1, Array2)

I need to insert criteria into the SumProduct function using
references to
variables. For example, I would like the following to work: Result
=
Application.SumProduct((Array1 0), (Array2 <= 2)) but it does not
function properly.

How do I insert criteria into the Application.SumProduct() syntax?

"Frank Kabel" wrote:

Hi
you can't use application.sumproduct as this kind of syntax is not
supported by VBA. Bout you could use
Evaluate("=SUMPRODUCT((B2:D12" & var_low & ")*(B2:D12<=" &
var_high & ") )")

--
Regards
Frank Kabel
Frankfurt, Germany


Scott P wrote:
Thanks, Pascal. Is there a way to accomplish the same thing
using
this type of syntax: Result = Application.SumProduct? I am going
to want to use variable references for the evaluation parameters
-- for example, two criteria might be: (B2:D12 Low) and (B2:D12
<= High) where Low and High are variables. I can't get the
following syntax to work: Result =
Application.SumProduct("(B2:D120)*(B2:D12<=2) )")

Alternatively, can I use the COUNTIF function to accomplish the
same end result? Thanks again.

"papou" wrote:

Hi Scott
Use SUMPRODUCT:
Evaluate("=SUMPRODUCT((B2:D120)*(B2:D12<=2) )")

HTH
Regards
Pascal

"Scott P" a écrit dans le
message de
...
I am trying to replicate a function such as this from Excel:
{=COUNT(IF((B2:D120)*(B2:D12<=2),B2:D12))} in VBA. How do I
evaluate multiple criteria using COUNTIF in VBA?