Hi Alvin,


Age NY IL
1-10 F1----------
11-20 F2----------
NY IL
Chest F3----------
Hand F4----------



Substitute F1,F2,F3,F4 with the following formulas and fill them to right as
shown
(right: ----------), suppose that the original table is in Sheet1:

F1:
=SUMPRODUCT(--(Sheet1!$A$2:$A$5<=VALUE(RIGHT($A2,2))),--(Sheet1!$B$2:$B$5=B$1))
F2:
=SUMPRODUCT(--(Sheet1!$A$2:$A$5=VALUE(LEFT($A3,2))),--(Sheet1!$B$2:$B$5=B$1))
F3:
=SUMPRODUCT(--(NOT(ISERROR(SEARCH($A5,Sheet1!$C$2:$C$5)))),--(Sheet1!$B$2:$B$5=B$1))
F4:
=SUMPRODUCT(--(NOT(ISERROR(SEARCH($A6,Sheet1!$C$2:$C$5)))),--(Sheet1!$B$2:$B$5=C$1))+3*SUMPRODUCT(--(NOT(ISERROR(SEARCH("both",Sheet1!$C$2:$C$5)))),--(Sheet1!$B$2:$B$5=C$1))

I don't understand what "Age has age bracket" means. Would you explain it?

I suppose that 11-20 as age limits should be 11-25, or age 25 should be 20,
otherwise age 25 doesn't belong to any of the age groups!

Regards,
Stefi