Guys... I feel like you're both pouring your guts out on this one, but
I'm definitely doing something wrong. When I entered Biff's function, I
get #NAME? returned...

Here's my setup:
Sheet1 --- (This is where I'd like to store the average of sheet2 &
sheet3 for each person on Sheet1...)
Bob
Jim
Joe
Joex
Liz
Sue
Tom

Sheet2...
Bob 93
Jim 26
Joe 34
Joe 100
Sue 34
Tom 82

Sheet3...
Bob 66
Joe 0
Joex 93
Liz 88
Sue 9
Tom 7

So in Sheet1, in B4, I inserted Biff's function and get #NAME
returned....


Biff Wrote:
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)
)



--
quailhunter
------------------------------------------------------------------------
quailhunter's Profile: http://www.excelforum.com/member.php...o&userid=27739
View this thread: http://www.excelforum.com/showthread...hreadid=477377