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