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