Sumproduct formula

[mlbdc2012]

I need some help coming up with a Sumproduct Formula for my worksheet. In my attached worksheet, I need to rank criteria based on criteria from a few different columns.

I want to take Column F and Break it into 2 separate columns (listed in my worksheet as Column G and H) and then rank those 2 columns based on criteria in columns D and F.

For example, Cell G4 should read text "C1" because the Player in Cell C4 is listed as both the first C from the AL and the first C listed in Column F. Cell H5 should then read text "1B1" because he is the first 1B listed from the NL in Column F. Cell G6 should read text "2B1" because the player listed is from the AL and is the first 2B listed in Column F.

In addition, Cell H7 should then be listed as "C1" also because he is from the NL and the first C listed from the NL in Column F.

Any help with this would be greatly appreciated. Please let me know if you have any questions. Thanks!

[daffodil11]

G4:

=IF($D4=G$2,$E4&SUMPRODUCT(($E$4:E4=$E4)*($D$4:D4=G$2)),"")

copy over and down

[mlbdc2012]

G4:

=IF($D4=G$2,$E4&SUMPRODUCT(($E$4:E4=$E4)*($D$4:D4=G$2)),"")

copy over and down

Perfect! Thanks for your help!

[daffodil11]

No problem.