"Fin Fang Foom" wrote...
....
. . . The mega formula you provided I'm getting the wrong
total. I'm getting 9 it should be 73.
....

With the following in foo!A3:B14,

a 1
s 5
d 4
e 5
f 8
r 9
t 7
r 9
h 3
y 6
u 4
j 4

and the following in bar!A3:B21

p 1
o 5
ui 4
jk 5
m 8
b 9
g 7
f 2
ds 3
r 6
h 4
ui 4
r 58
k 25
r 58
ds 1
sw 2
gbv 5
jn 4

and the following in D3:D4 in the active worksheet,

foo
bar

and the name seq defined as =ROW($1:$194)-1

then the following ARRAY formula

=SUM((MATCH(T(OFFSET(INDIRECT("'"&T(OFFSET($D$3:$D $4,seq/97,0,1,1))
&"'!A3"),MOD(seq,97),0))&N(OFFSET(INDIRECT("'"&T(O FFSET($D$3:$D$4,
seq/97,0,1,1))&"'!B3"),MOD(seq,97),0)),T(OFFSET(INDIRE CT("'"
&T(OFFSET($D$3:$D$4,seq/97,0,1,1))&"'!A3"),MOD(seq,97),0))
&N(OFFSET(INDIRECT("'"&T(OFFSET($D$3:$D$4,seq/97,0,1,1))&"'!B3"),
MOD(seq,97),0)),0)=seq+1)*(T(OFFSET(INDIRECT("'"&T (OFFSET($D$3:$D$4,
seq/97,0,1,1))&"'!A3"),MOD(seq,97),0))="r")*N(OFFSET(I NDIRECT("'"
&T(OFFSET($D$3:$D$4,seq/97,0,1,1))&"'!B3"),MOD(seq,97),0)))

returns 73 on my system, running Excel 2003 SP1.


Here is a small example in worksheet(2) it contains the following,
(A) (B)
r 9
s 5
d 4
r 9
f 8


Worksheet(3)
(A) (B)
p 1
o 5
r 58
k 25
r 58
b 9
g 7
r 6

The data I just provided above should look for "r" in column A that
has the unique vaules in column B and the total should be 73.

Yes, and when I replace your first sample data with this new sample data, I
still get 73 from my formula. Are YOU certain your second worksheet name, in
cell D4, is the name of the worksheet that contains your second data subset?
I can get my formula to return 9 too by changing D4 to the name of a blank
worksheet, and presumably if it weren't blank but had no records with "r" in
col A and new distinct values in col B.

Note: I only provided the monster formula to demonstrate that it could be
done using only built-in functions. I still think it's a VERY BAD IDEA to
use it because it's very slow and very complex.