SUMPRODUCT formula

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

You're MUCH more likely to get help if you post a small sample file.

Pictures of your sheet are really not worth much.

Originally Posted by Cutter

You're MUCH more likely to get help if you post a small sample file.

Pictures of your sheet are really not worth much.

ok, any advice on the easiest way to post a small sample file w/o losing the formatting?

i've tried copying and trying to paste as a small bmp, but it's still too large

I tried pasting into a word doc... is this what you're looking for?

here we go! in excel form...

this last attachment has the working formulas if you scroll to the right of the data.

thank you!

Originally Posted by brooksc29

I'm not sure how to post this work easily through the

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

I am trying to write formulas for the following:
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.

I have figured out the formula for filtering for Inning, Catcher, and Pitcher... I just need to figure out how to add to it, if I want ALL Pitchers or ALL Catchers, how do I add that piece to the formula...

this is the formula I have now, as an array
=IFERROR(MODE(IF($B2:$B31210=$W$2,IF($E2:$E31210=$W$3,IF($D2:$D31210=$W$5,IF($G$2:$U$31208=$W$4,IF($G$3:$U$31209=1,IF(ISNUMBER($G$4:$U$31210),$G$4:$U$31210))))))),"-")

I would think that if you want ALL pitchers then you would remove that portion of the formula that specifies the pitcher.

Try

=IFERROR(MODE(IF($E2:$E31210=$W$3,IF($D2:$D31210=$W$5,IF($G$2:$U$31208=$W$4,IF($G$3:$U$31209=1,IF(ISNUMBER($G$4:$U$31210),$G$4:$U$31210)))))),"-")

Similarly you would remove the Catcher specification to get ALL catchers.

I also think the merged cells are going to cause problems for you.