Countif in VBA -- evaluate multiple criteria
 

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?

Countif in VBA -- evaluate multiple criteria
 

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?

Countif in VBA -- evaluate multiple criteria
 

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?

Countif in VBA -- evaluate multiple criteria
 

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?

Countif in VBA -- evaluate multiple criteria
 

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?

Countif in VBA -- evaluate multiple criteria
 

Hi Scott
as said this kind of syntax is NOT supported within VBA. Therefore use
Evaluate as shown in the previous post!

--
Regards
Frank Kabel
Frankfurt, Germany


Scott P wrote:
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?

Countif in VBA -- evaluate multiple criteria
 

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?

Countif in VBA -- evaluate multiple criteria
 

Thanks to Tom and Frank. As I understand it, array formulas are supported in VBA. For example, I have used Application.PercentRank() as a formula in VBA -- the syntax of percentrank is listed as PERCENTRANK(array,x,significance) in Excel's help file.

Instead of using multiple conditions within one statement, I am now using this syntax: Res = Application.CountIf(Array1, "" & Low) - Application.CountIf(Array1, "=" & High)

This seems to work well. Thanks again.

"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?

Countif in VBA -- evaluate multiple criteria
 

Hi Scott
yes this kind of array formula is supported BUT MS has decided (who
knows why) not to support this 'special' kind of SUMPRODUCT formula or
COUNTIF formula in VBA.
In addition your PERCENTRANK function just accepts an array as
parameter but I wouldn't name it an array formula :-)

--
Regards
Frank Kabel
Frankfurt, Germany


Scott P wrote:
Thanks to Tom and Frank. As I understand it, array formulas are
supported in VBA. For example, I have used Application.PercentRank()
as a formula in VBA -- the syntax of percentrank is listed as
PERCENTRANK(array,x,significance) in Excel's help file.

Instead of using multiple conditions within one statement, I am now
using this syntax: Res = Application.CountIf(Array1, "" & Low) -
Application.CountIf(Array1, "=" & High)

This seems to work well. Thanks again.

"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?

Countif in VBA -- evaluate multiple criteria
 

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?

Countif in VBA -- evaluate multiple criteria
 

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?

Countif in VBA -- evaluate multiple criteria
 

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?