I am trying to generate information for use in my Fantasy Baseball draft. I have an Excel spreadsheet (Excel Starter) that I use to rank the available players to assist me in my drafting. I use the sheet for a number of different leagues, so the information needs to have some variability. I have an algorithm I wrote that determines the 'scarcity' of a players position and a major component is how many teams are in the league. Obviously, with more teams it may be best to select a player from a particular position earlier in the draft.

So, on my first sheet I have all the players listed by how they rank according to my algorithms. In column E, the players' position is listed. On my last sheet, I want to determine position 'scarcity' using how many players of a particular position would be available for each draft round. So, with 10 teams, there are 10 players chosen during each round. If I search the first 10 players from my rankings based upon position, I may find 3 catchers. But, if there are 12 teams in the league, than number could change.

I am using the following formula to sort by catchers in the first 10 players:

=COUNTIF(RANK!E3:E12,"C")

It searches the first 10 cells for the "C" (catcher).

What I need is for the "12" to be variable based upon the number of teams. If there are 12 teams, it should be:

=COUNTIF(RANK!E3:E14,"C")

That would search the first 12 cells for the "C" (catcher)

If I place a cell within the last sheet that I enter the number of teams, how do I transfer that number into the COUNTIF formulation?

Also, for the next round of the draft, the players to sort on are in cells E13 to E22 (for 10 teams), so BOTH row/column references need to be variable.

Any suggestions?