Bit difficult to be precise, without knowing the data etc.

A few thoughts.

Are you sure that rng_sum has those areas that you address in the code, you
do no checking?

Why do you hard code Location in the code? What is the relationship between
Location that you pass through the rng argument, and the range("Location")?

Show us what the data looks like.

--

HTH

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


"xcelion" wrote in
message ...

Hi Bob,

Your solution is working but iam unable to use it my case

Iam writting custom fuction where the parametes to this array formula
are passed as arguments of the UDF like this




Code:
--------------------
Function MyCountIf(rng As range, strCriteria As String, rng_sum As
range) As Long
Dim lCount As Long

For i = 1 To range("Location").Areas.Count
lCount = lCount +
ActiveSheet.Evaluate("=COUNT(IF(rng.Areas(i)=strCr iteria,IF(rng_sum.Areas(i)
,1)))")


Next i
MyCountIf = lCount
End Function
--------------------


I should be able to call the fuction like this

=MyCountIf(Location,"NY",Headcount)

In this fuction iam getting error on the assignment
rng.Areas(i)=strCriteria Type Mismatch

Can you please advice

Thanks
Xcelion


--
xcelion
------------------------------------------------------------------------
xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=485839