SUMPRODUCT formula help

B

brooksc29

Active Member
Joined
Jul 25, 2010
Messages
333
I'm not sure how to post this work easily through the thread, but here's a link to my work
http://s1015.photobucket.com/albums/...urrent=A-1.jpg

if you can view it it will be much easier to help me.

To start, I have named ranges on the worksheet:

A2:A31208 = DateRange
B2:B31208 = Pitcher
C2:C31208 = Batter
D2:D31208 = Inning
E2:E31208 = Catch
G2:U31210 = AllP

in colums A:E data is entered into rows 2, 5, & 8, etc all the way down to 31208, in every 3 cells, as every three cells are merged together. I have it set up that way b/c in G2:G31210 each row corresponds to either a Pitch (row 1, 4, 7, etc) a starting point (row 2, 5, 8, etc) and a finishing point (row 3, 6, 9, etc). I am trying to write formulas based upon the Date Range, name of Pitcher, Batter, Inning, or Catcher and where the Pitch Type (row 1, 4, 7) finished (row 3, 6, 9) for each place it started (row 2, 5, 8).

I have some drop down lists that lets the user choose the name of the pitcher, catcher, inning, or batter, and choose a date range. those cells will be referenced in the formulas that will determine the results the user is looking for.

In cell W2, I have the pitcher name the user chooses from a drop down list
In cell W3, I have the catcher name (options are All or choice of two catchers)
In cell W4, I have the Pitch Type (F, C, S, H, or All)
In cell W5, I have the Inning (options are 1,2,3,4,5,6,7,8,9, 1-3, 4-6, 7-9, or ALL)

There are 9 quadrants where I will be filtering information for, simply given numerical values 1-9. Right now I will use an example of quadrant 1.

I am trying to write formulas for the following (all entered as arrays):
1) For the most common finishing point for Pitch Types to a location (quadrant 1) I have, as an array:
=IFERROR(MODE(IF($B2:$B31210=$W$2,IF($G$2:$U$31208=$W$4,IF($G$3:$U$31209=1,IF(ISNUMBER($G$4:$U$31210 ),$G$4:$U$31210))))),"-")

This formula is figuring out most common quadrant that the pitch type in cell W4 finished in, that started in quadrant 1, for the pitcher name in cell W2. I need to add to this formula the ability to filter for inning, catcher, and batter, and date range, and I'm not sure how to do that. I also need the ability for filtering for ALL pitchers, ALL innings, ALL catchers, as well.

2) For total Pitch Types to a location (quadrant 1) I have, as an array:
=IFERROR(SUM(IF($B2:$B31210=$W$2,IF($G$2:$U$31208=$W$4,IF($G$3:$U$31209=1,$G$3:$U$31209)))),"-")

This formula is simply truing to total the number of pitch types in cell W4 for the quadrant 1 by the pitcher name in cell W2. Same as above, I need to add the ability to SUM when the user chooses a certain Catcher, Inning, Batter, Date Range, as well as the ALL function of the formula.

3) For total number of pitch type that didn't finish in same quadrant where they started
=SUMPRODUCT(($G2:$U$31208=$W$4)*($G3:$U$31209=1)*($G3:$U$31209<>$G4:$U$31210))

This formula is totaling the number of pitch type in cell W4, that finished in a zone different than quadrant 1. I need to add the ability to filter for the same as above. I have tried using this: =SUMPRODUCT(Pitcher=$W$2)*(CATCH=$W$3)*(Inning=$W$5)*(AllP=$W$4) - but it's giving me back 0 as a result. I'm wondering if it has to do w/ the merging of cells for Pitcher/Catch/Inning?

I realize this is an extremely long post and any help would be much appreciated!

Thank you so much!

p.s. I have also posted this thread on another site (http://www.excelforum.com/excel-general/773668-sumproduct-formula-help.html) for help. I am just hoping to increase my chances of assistance. I do not mean to offend anyone here.

Thank you!
 
Sort by date Sort by votes
<TABLE style="WIDTH: 538pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=714><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" span=4 width=42><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21 width=64>DATE</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; WIDTH: 99pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=132>PITCHER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=100>BATTER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>INN</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 92pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=122>CATCHER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=42>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=42>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=42>3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=42>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 height=61 rowSpan=3>4/1/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 rowSpan=3>LEWIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl75 rowSpan=3>SMITH</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 rowSpan=3>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 rowSpan=3>JONES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>PITCH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>S</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>H</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>START</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=21>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>12</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 height=61 rowSpan=3>4/2/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 rowSpan=3>LEWIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl75 rowSpan=3>DAVIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 rowSpan=3>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 rowSpan=3>JONES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>PITCH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>S</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>H</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>START</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>2</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=21>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>12</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 height=61 rowSpan=3>4/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 rowSpan=3>LEWIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl75 rowSpan=3>WELLS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 rowSpan=3>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 rowSpan=3>JONES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>PITCH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>S</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>H</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>START</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>2</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=21>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>12</TD></TR></TBODY></TABLE>

This is A1:J10

The result of the formula you asked about is 0, when using ALL in W2, W3, W4, W5... the result should be 10.
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
brooksc29 said:
<TABLE style="WIDTH: 538pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=714><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" span=4 width=42><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=21 width=64>DATE</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; WIDTH: 99pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=132>PITCHER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=100>BATTER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>INN</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 92pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=122>CATCHER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=42>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=42>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=42>3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=42>4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 height=61 rowSpan=3>4/1/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 rowSpan=3>LEWIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl75 rowSpan=3>SMITH</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 rowSpan=3>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 rowSpan=3>JONES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>PITCH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>S</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>H</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>START</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=21>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>12</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 height=61 rowSpan=3>4/2/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 rowSpan=3>LEWIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl75 rowSpan=3>DAVIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 rowSpan=3>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 rowSpan=3>JONES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>PITCH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>S</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>H</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>START</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>2</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=21>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>12</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl81 height=61 rowSpan=3>4/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 rowSpan=3>LEWIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl75 rowSpan=3>WELLS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 rowSpan=3>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 rowSpan=3>JONES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>PITCH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>S</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>H</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>START</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>2</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=21>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>12</TD></TR></TBODY></TABLE>

This is A1:J10

The result of the formula you asked about is 0, when using ALL in W2, W3, W4, W5... the result should be 10.
Click to expand...

I don't know a thing about pichers, catchers, etc. (except thru Charlie Brown)... Be nice: Create a few sets of W2:W5 values, corresponding correct results. Some sets with no ALL anywhere, others with ALL...
 
Upvote 0
Aladin Akyurek said:
I don't know a thing about pichers, catchers, etc. (except thru Charlie Brown)... Be nice: Create a few sets of W2:W5 values, corresponding correct results. Some sets with no ALL anywhere, others with ALL...
Click to expand...

ok, here a snap shot of the data and the cells W2:W5 with the formulas I am using in column X.

<TABLE style="WIDTH: 687pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=912><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" span=4 width=42><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 3218" width=88><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70 height=21 width=64>DATE</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: black; WIDTH: 99pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=132>PITCHER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=100>BATTER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>INN</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 92pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=122>CATCHER</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: black; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=64> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=42>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=42>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=42>3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; WIDTH: 32pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=42>4</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 66pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 width=88></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 83pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63 width=110></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl91 height=61 rowSpan=3>4/1/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 rowSpan=3>LEWIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl79 rowSpan=3>SMITH</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 rowSpan=3>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl85 rowSpan=3>JONES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>PITCH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>S</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>H</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63>BUSH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>START</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63>JONES</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>12</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl91 height=61 rowSpan=3>4/2/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 rowSpan=3>BUSH</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl79 rowSpan=3>DAVIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 rowSpan=3>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl85 rowSpan=3>MAIN</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>PITCH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>S</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>H</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>START</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>12</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 45.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl91 height=61 rowSpan=3>4/3/2011</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 rowSpan=3>LEWIS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl79 rowSpan=3>WELLS</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl82 rowSpan=3>1</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl85 rowSpan=3>JONES</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>PITCH</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>S</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>F</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>H</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl74></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>START</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=21>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>1</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>11</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>12</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl63></TD></TR></TBODY></TABLE>

W2:W5's values are determined by a drop down list by the user on another worksheet. For this instance the user entered Bush in W2 (PitcherName - B2:B8), Jones in W3 (CatcherName - E2:E8), F in W4 (AllP - G2:G8), and 1 in W5 (Inning - D2:D8)... no ALLs in this set.

as of right now, the SUMPRODUCT formula is the only one not working for ALL...
{=SUMPRODUCT((B2:B8=IF(B2:B8="ALL",B2:B8,$W$2))*(E2:E8=IF(E2:E8="ALL",E2:E8,$W$3))*(D2:D8=IF(D2:D8="ALL",D2:D8,$W$5))*(G2:G8=IF(G2:J8="All",G2:J8,$W$4))*(GameDate>=StartDate)*(GameDate<=EndDate)*(G3:J9<>G4:J10))}

in this instance, the result I am getting is 1... which is correct.
if W2 were to = Lewis, and W3 = Jones, then the result is 3... which is correct.

As soon as I change any of W2:W5 to ALL, I get 0 as a result.

the SUMIF formula below works with ALL or specific criteria.
{=IFERROR(SUM(IF(B2:B8=IF($W$2="All",B2:B8,$W$2),IF(E2:E8=IF($W$3="All",E2:E8,$W$3),IF(D2:D8=IF($W$5="All",D2:D8,$W$5),IF(G2:J8=IF($W$4="ALL",G2:J8,$W$4),IF($G$3:$J$9=1,$G$3:$J$9)))))),"-")}...

I think I just realized what the problem is in the SUMPRODUCT formula...
{=SUMPRODUCT((B2:B8=IF(B2:B8="ALL",B2:B8,$W$2))*(E2:E8=IF(E2:E8="ALL",E2:E8,$W$3))*(D2:D8=IF(D2:D8="ALL",D2:D8,$W$5))*(G2:G8=IF(G2:J8="All",G2:J8,$W$4))*(GameDate>=StartDate)*(GameDate<=EndDate)*(G3:J9<>G4:J10))}

this is saying if B2:B8 (and all other ranges) = ALL, when I really need to say if W2:W5 = ALL. I think if I change that... cross my fingers... it should work, right?
 
Upvote 0
ok... ALL now seems to be working for the SUMPRODUCT...

I need to work the GameDate>=StartDate and GameDate<=EndDate into this formula...

=IFERROR(MODE(IF(PitcherName=IF($W$2="All",PitcherName,$W$2),IF(CatcherName=IF($W$3="All",CatcherName,$W$3),IF(Inning=IF($W$5="All",Inning,$W$5),IF(AllP=IF($W$4="ALL",AllP,$W$4),IF(PitchStart=1,IF(ISNUMBER(PitchFinish),PitchFinish))))))),"-")

I tried simply putting IF(GameDate>=StartDate), IF(GameDate<=EndDate) and it has a problem with StartDate. StartDate is the beginning of the Date Range that the user will enter. In this case 4/1/11...

it's probably b/c I'm not sure what to put if the value is true, and false, after the StartDate
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question Question
Need help with a formula to correctly subtract decimals with a base of 3 not 10
Replies
3
Views
422
Gusnk
G
J
  • Locked
  • Question Question
formula for randomly choosing an option
Replies
1
Views
163
Fluff
Fluff
N
  • Solved
Sumproduct with Table Rows doesn't work
Replies
8
Views
308
Nicha
N
E
  • Solved
Divide a number into multiple results
Replies
7
Views
122
djclements
djclements
A
  • Solved
SUMPRODUCT Help
Replies
5
Views
146
AYouQueTai
A

Forum statistics

Threads
1,224,735
Messages
6,180,638
Members
452,992
Latest member
TokugawaIesuma

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top