Without knowing your actual data structure, here's what I came up with:

=SUMPRODUCT((Wk 1!$B$33:$B$55=Sheet17!A2)*(Wk 1!C32:W32='Indiv
Stats'!B4)*(Wk 1!C33:W55))
+SUMPRODUCT((Wk 1!$B$90:$B$111=Sheet17!A2)*(Wk 1!C89:W89='Indiv
Stats'!B4)*(Wk 1!C90:W111))
+SUMPRODUCT((Wk 1!$Z$33:$Z$55=Sheet17!A2)*(Wk 1!AA32:AU32='Indiv
Stats'!B4)*(Wk 1!AA33:AU55))
+SUMPRODUCT((Wk 1!$Z$90:$Z$111=Sheet17!A2)*(Wk 1!AA89:AU89='Indiv
Stats'!B4)*(Wk 1!AA90:AU111))

That reduces your formula from 840 characters to around 370

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)

"top.jimmy" wrote in message
...
I have four duplicate match reports on the 'WK 1'! spreadsheet. One is
used
for each match between 2 teams each week. 8 teams total in a leaque. I
was
wondering how to write an Index/Match function that would search the whole
sheet (rather than each match report within the sheet) to index & match
the
criteria then return the desired number. Not quite sure how to write a
shorter version.

Here's what I have: Columns go from A:AU, Rows go from 32:111
A2= team member name
'Indiv Stats'!B4= Stat to search for

=IF(ISNA(INDEX('WK 1'!$A$32:$W$56,MATCH(A2,'WK
1'!$B$32:$B$55,0),MATCH('Indiv Stats'!B4,'WK
1'!$A$32:$W$32,0))),0,(INDEX('WK
1'!$A$32:$W$56,MATCH(A2,'WK 1'!$B$32:$B$55,0),MATCH('Indiv Stats'!B4,'WK
1'!$A$32:$W$32,0))))+IF(ISNA(INDEX('WK 1'!$Z$32:$AU$56,MATCH(A2,'WK
1'!$Z$32:$Z$55,0),MATCH('Indiv Stats'!B4,'WK
1'!$Z$32:$AU$32,0))),0,(INDEX('WK 1'!$Z$32:$AU$56,MATCH(A2,'WK
1'!$Z$32:$Z$55,0),MATCH('Indiv Stats'!B4,'WK
1'!$Z$32:$AU$32,0))))+IF(ISNA(INDEX('WK 1'!$B$89:$W$111,MATCH(A2,'WK
1'!$B$89:$B$111,0),MATCH('Indiv Stats'!B4,'WK
1'!$B$89:$W$89,0))),0,(INDEX('WK 1'!$B$89:$W$111,MATCH(A2,'WK
1'!$B$89:$B$111,0),MATCH('Indiv Stats'!B4,'WK
1'!$B$89:$W$89,0))))+IF(ISNA(INDEX('WK 1'!$Z$89:$AU$111,MATCH(A2,'WK
1'!$Z$89:$Z$111,0),MATCH('Indiv Stats'!B4,'WK
1'!$Z$89:$AU$89,0))),0,(INDEX('WK 1'!$Z$89:$AU$111,MATCH(A2,'WK
1'!$Z$89:$Z$111,0),MATCH('Indiv Stats'!B4,'WK 1'!$Z$89:$AU$89,0))))

You help is appreciated,
JIM