Finding most often occurances (MODE?)

[brooksc29]

I am trying to sort through a series of data that is inputed as follows:

A B C D E F G H I J
1 DATE PITCHER BATTER INNING CATCHER 1 2 3 4 5

2 4/1/11 JONES SMITH 1 HILL FB FB SL SL FB

3 1 1 2 2 1

4 4 4 1 2 3


Cells A2:A4 are merged as are the same cells in columns B:E.

What I am trying to sort through is the data in F2:J4 (on my worksheet it actually goes to U4 but for the sake of saving space I only am showing an abbreviated version).

I'd like to write a formula that will interpret the data and look for the most common number in row 4 for each instance of FB or SL in row 2 when it has a "1" in row 3...

so something like - MODE(IF(F2:J2="FB" & F3:J3="1", F4:J4)...

I also have a few questions on if this is even the best way to input the data? The problem lies in that there is going to be a very very large amount of FB's and SL's entered (many thousand) and I need to dig through all the data and be able to sort by COL A, COL B, COL C, COL D, or COL E while still finding the mode for F:J. If I try to enter each FB or SL on its own row and seperate the rest into their own columns, I am afraid there will be too many rows in the data set and the performance of the formula will suffer.

Will I be able to apply a formula to dig through all the data that will be set up this way through a range of F2:U2000? will it work easiest with this set up this way?

Any help the wizards on here would be greatly appreciated!! I am also happy to send along a better version of what this setup looks like.

Thank you very much

 

[brooksc29]

i realize that data example didn't come through with how I had intended it to. the data in rows 3 & 4

3 1 1 2 2 1

4 4 4 1 2 3

is supposed to be in columns F:J