Count match of two criteria

A

Adrian

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

Bob Phillips

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)
 
T

Tom Ogilvy

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
 
A

Adrian

Tom, and Bob,
Many thanks to you both.

Tom Ogilvy said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top