Can this formula be reduced?

[top.jimmy]

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

 

[Ron Coderre]

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]

Thanks Ron:
WOW! That's helpful for sure...I never thought of using the Sumproduct
function. I'm going for the 'One-liner'. )

A each player's name only appears once on 'WK 1'!; since each player plays
in only one game per week. So I'm wondering if it's possible to search/Index
all four matches(games) for a player's name. In this case; 'A2', then match
the stat. Can the Sumproduct function search the whole sheet 'WK 1'! for the
player's name, then essentially match the particluar stat, 'Indiv Stats'!B4?
It looks like the four Sumproducts you wrote below following my original
formula can be reduced even further to a one line Sumproduct function. I
just don't know how to do it. I'm also looking to eliminate the use of
absolute cell references '$', since using them obviously makes it hard to
just copy a formula to successive cells.