Harlan

Thank you for your formula. For completeness, I've adjusted the references
and got it to work as:

=SUMPRODUCT((L$2=INDIRECT(Admin!$A$19&"'!$A$"&INDE X(Admin!$E:$E,$C$2)&":$A$"&INDEX(Admin!$F:$F,$C$2) ))*(""<INDIRECT(Admin!$A$19&"'!$AA$"&INDEX(Admin! $E:$E,$C$2)&":$AA$"&INDEX(Admin!$F:$F,$C$2)))/COUNTIF(INDIRECT(Admin!$A$19&"'!$AA$"&INDEX(Admin! $E:$E,$C$2)&":$AA$"&INDEX(Admin!$F:$F,$C$2)),INDIR ECT(Admin!$A$19&"'!$AA$"&INDEX(Admin!$E:$E,$C$2)&" :$AA$"&INDEX(Admin!$F:$F,$C$2))&""))

I've not used the 'index' function before, so it is interesting to see how
you can have two formula (Pete_UK's and yours) come up with the same answer.

Thanks

Guy


"Harlan Grove" wrote:

Guy wrote...
I have the following formula that works but I want to be able to
change some more of the direct references into indirect, . . .
. . . Specifically I want to change every reference to 'fred.xls'
to reference a cell that contains a filename as text, and I want
to change every instance of the value '15' to refernce a cell that
contains the value. I hope that makes sense.

=SUMPRODUCT((INDIRECT("'[Fred.xls]P1-P2-P3'!$A$"
&Admin!$E$15&":$A$"&Admin!$F$15)=L$2)
*(INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"
&Admin!$E$15&":$AA$"&Admin!$F$15)<"")
/COUNTIF(INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"&
Admin!$E$15&":$AA$"&Admin!$F$15),
INDIRECT("'[Fred.xls]P1-P2-P3'!$AA$"
&Admin!$E$15&":$AA$"&Admin!$F$15)&""))

Try it in stages. If the filename would be in cell Admin!X99, first
try

=SUMPRODUCT((INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$A$"
&Admin!$E$15&":$A$"&Admin!$F$15)=L$2)
*(INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"
&Admin!$E$15&":$AA$"&Admin!$F$15)<"")
/COUNTIF(INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"
&Admin!$E$15&":$AA$"&Admin!$F$15),
INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"
&Admin!$E$15&":$AA$"&Admin!$F$15)&""))

If that works, then if the 15s you mean are the row portions of the
references Admin!$E$15 and Admin!$F$15, then if the 15 were in Admin!
Y99, try

=SUMPRODUCT((L$2=INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$A$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$A$"&INDEX(Admin!$F:$F, Admin!$Y$99)))
*(""<INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$AA$"&INDEX(Admin!$F:$F ,Admin!$Y$99)))
/COUNTIF(INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$AA$"&INDEX(Admin!$F:$F ,Admin!$Y$99)),
INDIRECT("'["& Admin!$X$99 &"]P1-P2-P3'!$AA$"&INDEX(
Admin!$E:$E,Admin!$Y$99)&":$AA$"&INDEX(Admin!$F:$F ,Admin!$Y$99))&""))