Using only built-in functions, it gets very ugly

It's not all that ugly and it works! Very nice!

I was headed in the right direction but was still a long ways off.

Wonder how the OP made out?

Biff

"Harlan Grove" wrote in message
oups.com...
Biff wrote...
That said, if all the values were positive *and* there were only one
entry matching A1 in each other worksheets' A3:A30 range, then this
should work (at least it works in my tests).

Yes, it works under those conditions, where there is only a single
instance
(or none) per sheet. I was working on a solution in which there were
multiple instances per sheet. How could that be done? Nothing I've tried
works. The difficult part is the count.
...

There are times it's a PITA that Excel really isn't a 3D spreadsheet.

The simplest solution would be using Laurent Longre's MOREFUNC.XLL
add-in, specifically, using that add-in's THREED function.

=SUMPRODUCT(--(THREED(A:Z!$A$4:$A$30)=A4),THREED(A:Z!$D$4:$D$30) )
/SUMPRODUCT((THREED(A:Z!$A$4:$A$30)=A4)*(THREED(A:Z !$D$4:$D$30)0))

Using only built-in functions, it gets very ugly, but it's possible. If
WSL, the named list of worksheets to process, were a horizontal array,

=SUMPRODUCT(
--(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4),
N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIRECT ("4:30"))-4,0,1,1))
)/SUMPRODUCT(
(T(OFFSET(INDIRECT("'"&WSL&"'!A4:A30"),ROW(INDIREC T("4:30"))-4,0,1,1))=A4)*
(N(OFFSET(INDIRECT("'"&WSL&"'!D4:D30"),ROW(INDIREC T("4:30"))-4,0,1,1))0)
)