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?

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?

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?

Scott
Yes you can use variables, in which case use the concatenation to build your
formula:
Result = Evaluate("=SUMPRODUCT((B2:D12" & Low & ")*(B2:D12<=" & High &
") )")

Regards
Pascal


"Scott P" a écrit dans le message de
...
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?

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?