N00b needs help counting :(

[Shooter McGavin]

I want to count each of the events in a session of "Baseball," a sweet drinkikng game.
Goals:
1. be able to count occurrences of "s" for each batter.
Tried: =SUM((C3:C14=”bruce”)*(D3:J14=”s″))
And got: #value!

2. do the same thing with three criteria
-inning in column "a"
-team in column "b"
-"r" in range (d3:j14)

3. if possible, have a stats sheet generated on the fly without previously setting up a batter, ie Bruce, Mike and Dan are set up but now Joel wants to play but isn't set up.

Please help and you can come learn this game and hang out with some ridiculous WhipperSnappers.

Thank you,
Shooter McGavin

PS Maybe we can go to Red Lobster sometime, too.

<table width="700" border="0" cellpadding="0" cellspacing="0"><col style="width: 41pt;" width="54"> <col style="width: 52pt;" width="69"> <col style="width: 75pt;" width="100"> <col style="width: 20pt;" width="26" span="7"> <col style="width: 53pt;" width="71"> <col style="width: 168pt;" width="224"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt; width: 41pt;" width="54" height="20">
</td> <td class="xl72" style="width: 52pt;" width="69">
</td> <td class="xl73" style="width: 75pt;" width="100">
</td> <td class="xl66" colspan="2" style="width: 40pt;" width="52">EVENT</td> <td class="xl67" style="width: 20pt;" width="26">
</td> <td class="xl67" style="width: 20pt;" width="26">
</td> <td class="xl67" style="width: 20pt;" width="26">
</td> <td class="xl67" style="width: 20pt;" width="26">
</td> <td class="xl68" style="width: 20pt;" width="26">
</td> <td class="xl72" style="width: 53pt;" width="71">
</td> <td class="xl73" style="width: 168pt;" width="224">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" height="20">INNING</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">TEAM</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">BATTER</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">2</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">3</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">4</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">5</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">6</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">7</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">DEFENDER</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">NOTES</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">whipper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">bruce</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">1</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">b</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">b</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">b</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">r</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">strike, strike, single, sb, sb, sb, run</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">whipper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">mike</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">k</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">strikeout</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">whipper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">dan</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">f</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">flyout</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">whipper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">bruce</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">g</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">sal</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">groundout, sal</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">snapper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">joel</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">g</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">bruce</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">groundout, bruce</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">snapper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">amanda</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">g</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">mike</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">groundout, mike</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">1</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">snapper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">sal</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">g</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">dan</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">groundout, dan</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" height="20">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">whipper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">mike</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">k</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">whipper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">dan</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">f</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">flyout</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">2</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">whipper</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">bruce</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">s</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">k</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl70" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl69" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>

 

[Aladin Akyurek]

Just taking up the following, assuming that the terms are correct...

=SUM((C3:C14=”bruce”)*(D3:J14=”s″))

requires that you confirm this formula with control+shift+enter, not just with enter.

A better way to express it is:

Control+shift+enter, not just enter...

=SUM(IF(C3:C14=”bruce”,IF(D3:J14=”s″,1)))