Count match of two criteria
 

I'm trying to write a VBA function to count the number of matches to two
criteria.
In a worksheet I can use SUMPRODUCT(--(range1="value1"),--(range2="value2"))
but I can't make it work in VBA.

range1 and range2 and value1 are variables, value2 is a string constant.

answer = Application.WorksheetFunction.SumProduct("--(" & range1 & "=""" &
value1 & """)", "--(" & range2 & "=""value2"")")

I am getting type mismatch errors. I suspect the problem is range1 and
range2 ; on a worksheet they are in the form A2:A300 but in VBA they aren't.

How to sort this out? Or is there a better method?

Count match of two criteria
 

Dim sformula As String

sformula = "SumProduct(--(" & range1.Address & "=""" & value1 & """)," &
_
"--(" & range2.Address & "=""" & value2 & """))"

answer = Evaluate(sformula)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Adrian" wrote in message
...
I'm trying to write a VBA function to count the number of matches to two
criteria.
In a worksheet I can use
SUMPRODUCT(--(range1="value1"),--(range2="value2"))
but I can't make it work in VBA.

range1 and range2 and value1 are variables, value2 is a string constant.

answer = Application.WorksheetFunction.SumProduct("--(" & range1 & "=""" &
value1 & """)", "--(" & range2 & "=""value2"")")

I am getting type mismatch errors. I suspect the problem is range1 and
range2 ; on a worksheet they are in the form A2:A300 but in VBA they
aren't.

How to sort this out? Or is there a better method?

Count match of two criteria
 

Array evaluation of this type is not supported in VBA. You have to use the
evaluate function to ask Excel to evaluate the formula for you:


answer = Evaluate("SumProduct(--(" & range1.Address & "=""" & _
value1 & """),--(" & range2.Address & "=""value2"")")

This will evaluate the activesheet using the array formula

--
Regards,
Tom Ogilvy



"Adrian" wrote in message
...
I'm trying to write a VBA function to count the number of matches to two
criteria.
In a worksheet I can use
SUMPRODUCT(--(range1="value1"),--(range2="value2"))
but I can't make it work in VBA.

range1 and range2 and value1 are variables, value2 is a string constant.

answer = Application.WorksheetFunction.SumProduct("--(" & range1 & "=""" &
value1 & """)", "--(" & range2 & "=""value2"")")

I am getting type mismatch errors. I suspect the problem is range1 and
range2 ; on a worksheet they are in the form A2:A300 but in VBA they
aren't.

How to sort this out? Or is there a better method?

Count match of two criteria
 

Tom, and Bob,
Many thanks to you both.

"Tom Ogilvy" wrote:

Array evaluation of this type is not supported in VBA. You have to use the
evaluate function to ask Excel to evaluate the formula for you:


answer = Evaluate("SumProduct(--(" & range1.Address & "=""" & _
value1 & """),--(" & range2.Address & "=""value2"")")

This will evaluate the activesheet using the array formula

--
Regards,
Tom Ogilvy



"Adrian" wrote in message
...
I'm trying to write a VBA function to count the number of matches to two
criteria.
In a worksheet I can use
SUMPRODUCT(--(range1="value1"),--(range2="value2"))
but I can't make it work in VBA.

range1 and range2 and value1 are variables, value2 is a string constant.

answer = Application.WorksheetFunction.SumProduct("--(" & range1 & "=""" &
value1 & """)", "--(" & range2 & "=""value2"")")

I am getting type mismatch errors. I suspect the problem is range1 and
range2 ; on a worksheet they are in the form A2:A300 but in VBA they
aren't.

How to sort this out? Or is there a better method?