Haven't read through that thread, far too long, b ut maybe

=SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$ F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0))))
-COUNTBLANK('477APN'!$F$5:$F$99)-COUNTBLANK('476APN'!$F$5:$F$99)-COUNTBLANK('478APN'!$F$5:$F$99)

--

HTH

Bob

"Steve" wrote in message
...
Howdee all.
I received some help from T. Valko back in January on a complicated
worksheet function. It's been working really great, and today I found that
I
wanted to make a modification to it.
So, Biff, if you'd be so kind as to one again help me on this, I'd
really...... appreciate it.

Original discussion. Dates back to January 4, 2010.

http://www.microsoft.com/communities...d-6f72a2f875a6

My goal today-- if posible-- is to get the names on 3 worksheets, into
this
formula, so that I can verify that all are there.
I did try the following, and it did not work.
'=SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$ F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0))))-COUNTBLANK('477APN'!$F$5:$F$99+'476APN'!$F$5:$F$14 +'478APN'!$F$5:$F$102&"")

The complaint that it came up with was that there was simply an error in
the
formula. And instead of selecting the part where the error was, the whole
formula was selected. (it occasionally will select just the part where the
error is.)

What I was hoping to do was to get 3 worksheet's ranges into the Match()
function's portion.

MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$F$14&" "&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0)

I tried placing a comma between each worksheet's ranges, but that just
tells
Match to do the next part of its function-- not good.
I then tried plus signs-- still no good.
Lastly, as you can see, I've placed ampersand symbols, hoping that it'd
see
that as more data to the function-- also no good.

as I was writing this, I realized that some other aspect might be causing
the failure. I was correct.
However, I still am not getting what I'd thought.

This is my correction for the present.
=SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&""&'476APN'!$F$5:$ F$14&""&'478APN'!$F$5:$F$102&"",$C$12:$C$51,0))))-COUNTBLANK('477APN'!$F$5:$F$99)-COUNTBLANK('476APN'!$F$5:$F$14)-COUNTBLANK('478APN'!$F$5:$F$102)
(and yes, I am doing the ctrl+shift+enter to activate the array function)

Thoughts, ideas?
I've created a single worksheet with all of the 3 ownerships, from 3
different worksheets. I'd now like to take the previous general equation
and
apply it to all 3 of the worksheets.

=SUMPRODUCT(--(ISNA(MATCH('477APN'!$F$5:$F$99&"",$C$12:$C$51,0)) ))-COUNTBLANK('477APN'!$F$5:$F$99)


=IF(ROWS(H$3:H3)G$3,"All Names Accounted
For",INDEX('477APN'!F$5:F$99,SMALL(IF(ISNA(MATCH(' 477APN'!F$5:F$99&"",C$12:C$51,0)),ROW('477APN'!F$5 :F$99)),ROWS(H$3:H3))-MIN(ROW('477APN'!F$5:F$99))+1))