Here is my data

and the Location is range defined for two areas under City and
Headcount for the areas under Headcount

That hardcoding of Location is not required ,i was testing the code


Survey 1 Survey 2
City Headcount City Headcount
NY 1 NY 1
NY 2 CA 1
TX 0 TX 1
NY 5 NY 1
TX 4 CA 1
TX 0 TX 0

I have similiar function wriiten for SUMIF and it's working fine


Code:
--------------------
Function MySumIf(rng As range, strCriteria As String, rng_sum As range) As Long
Dim sum As Long
For i = 1 To range("Location").Areas.Count
sum = sum + WorksheetFunction.SumIf(rng.Areas(i), strCriteria, rng_sum.Areas(i))
Next i
MySumIf = sum
End Function
--------------------




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