MODE IF using INDIRECT

[brooksc29]

what type of formula would I need to say

If G2:U80 = "FB"
and the cell below FB says "1"
then find the MODE for the cell below "1"

typically the data is arranged like
row 1 FB
row 2 1
row 3 6
row 4 FB
row 5 1
row 6 A
row 7 FB
row 8 1
row 9 6

the data goes across the row from col G:U in the same pattern

thank you very much for your help! this is a confusing project!

for more information on this project I can either email you my work or you can read another thread where I haven't received a response yet.
http://www.mrexcel.com/forum/showthread.php?t=545394

 

[brooksc29]

even if the number that was in row 4 is now a letter as well?

 

[Aladin Akyurek]

even if the number that was in row 4 is now a letter as well?

MODE is calculated over numbers, so...

CSE...

=MODE(IF(C2:C78="SMITH",IF(G2:U78="FB",IF(G3:U79="A",IF(ISNUMBER(G4:U80),G4:U80)))))

 

[brooksc29]

I understand. And I may have to request that we use numbers so the MODE is easier to calculate, but the data that has been collected already has letters... column C and column G now look like this.

<table border="0" cellpadding="0" cellspacing="0" width="153"><col style="width: 80pt;" width="106"> <col style="width: 35pt;" width="47"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt; width: 80pt;" width="106" height="21">BATTER</td> <td class="xl68" style="border-left: medium none; width: 35pt;" width="47">1</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl80" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">SMITH</td> <td class="xl67" style="border-left: medium none;">FB</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt; border-left: medium none;" height="20">A</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl66" style="height: 15.75pt; border-left: medium none;" height="21">A</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt; border-top: medium none;" height="21">BATTER</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl71" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">SMITH</td> <td class="xl67" style="border-left: medium none;">FB</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt; border-left: medium none;" height="20">A</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl66" style="height: 15.75pt; border-left: medium none;" height="21">A</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl68" style="height: 15.75pt; border-top: medium none;" height="21">BATTER</td> <td class="xl68" style="border-top: medium none; border-left: medium none;">1</td> </tr> <tr style="height: 15pt;" height="20"> <td rowspan="3" class="xl71" style="border-bottom: 1pt solid black; height: 45.75pt; border-top: medium none;" height="61">SMITH</td> <td class="xl67" style="border-left: medium none;">FB</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt; border-left: medium none;" height="20">A</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl66" style="height: 15.75pt; border-left: medium none;" height="21">A</td> </tr> </tbody></table>