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!
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!
