Hi. My boss have this table.
Age State Procedure
1 NY chest,hand
5 NY chest,hand
3 IL Both hand
25 IL R hand

And he wants to sort and tally them like
Age NY IL
1-10 2 1
11-20 1
NY IL
Chest 2
Hand 2 5

Can anyone give me a routine. Both hand count twice for left and right.
Age has age bracket. Thanks in advance.

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

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 .

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 .