I see your point, this is a dynamic solution with the disadvantage that you
have use 3 helper cells on sheet1, say D1,E1,F1 (I didn't find any simpler
solution):

In sheet1
D1: ="Sheet1!A2:A"&COUNT($A:$A)+1
E1: ="Sheet1!B2:B"&COUNT($A:$A)+1
F1: ="Sheet1!C2:C"&COUNT($A:$A)+1

In sheet2
Age NY IL
1-10 F1----------

F1:
=SUMPRODUCT(--(INDIRECT(Sheet1!$D$1)=VALUE(LEFT($A2,2))),--(INDIRECT(Sheet1!$D$1)<=VALUE(RIGHT($A2,2))),--(INDIRECT(Sheet1!$E$1)=B$1))

In sheet3
NY IL
Chest F3----------

F3:
=SUMPRODUCT(--(NOT(ISERROR(SEARCH($A2,INDIRECT(Sheet1!$F$1))))),--(INDIRECT(Sheet1!$E$1)=B$1))+IF($A2="hand",3*SUMPR ODUCT(--(NOT(ISERROR(SEARCH("both",INDIRECT(Sheet1!$F$1))) )),--(INDIRECT(Sheet1!$E$1)=B$1)),0)

and you can fill down and right.

Regards,
Stefi



"alvin" wrote:

thanks stefi.

Your right the age limit was a mistype. will it be possible to have the
address value placed at another cell so that it can be made dynamic?
instead of sheet1!$a$2:$a$5, which is static, what if you have
additional range/cells .