Hi!

For what it's worth.......

Some of the "biggest hitters" there are have replied to this thread. If they
can't do it, it can't be done!

At least one of the formulas offered here should work so I'm guessing that
you're missing some minor detail.

If you want to send me a copy of your file I'll take a look. Just let me
know how to contact you.

Biff

"quailhunter"
wrote in message
...

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