Countif in VBA -- evaluate multiple criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to replicate a function such as this from Excel: {=COUNT(IF((B2:D12>0)*(B2:D12<=2),B2:D12))} in VBA. How do I evaluate multiple criteria using COUNTIF in VBA?
 
Hi Scott
Use SUMPRODUCT:
Evaluate("=SUMPRODUCT((B2:D12>0)*(B2:D12<=2) )")

HTH
Regards
Pascal

Scott P said:
I am trying to replicate a function such as this from Excel:
Click to expand...
{=COUNT(IF((B2:D12>0)*(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:D12>0)*(B2:D12<=2) )")

Alternatively, can I use the COUNTIF function to accomplish the same end result? Thanks again.
 
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 said:
Thanks, Pascal. Is there a way to accomplish the same thing using this
Click to expand...
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:D12>0)*(B2:D12<=2) )")
 
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 &
") )")
 
Hi Scott
as said this kind of syntax is NOT supported within VBA. Therefore use
Evaluate as shown in the previous post!
 
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:D12>0)*(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 said:
Thanks for the help, Pascal and Frank. In terms of VBA support for that
Click to expand...
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
Click to expand...
variables. For example, I would like the following to work: Result =
Application.SumProduct((Array1 > 0), (Array2 <= 2)) but it does not function
properly.
 
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.
 
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 :-)
 
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?
 
Hi
try
res = Evaluate("COUNT(IF(("& Range1.address &">"& var1 &")*("&
Range1.address &"<="& var2 &"),"& Range2.address &"))")
 
Back
Top