On Aug 16, 12:16 pm, Fin Fang Foom wrote:
On Aug 16, 9:42 am, "Harlan Grove" wrote:



"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.- Hide quoted text -

- Show quoted text -

The data in worksheet(2) is not subset. I will use your udf instead,
its to bad I cannot attach file here so I could show you my problem I
have. My workbook only has 12 worksheets and range 20 to 50 rows each
in those worksheet.

Thank You very much Harlan Grove for your help

I got it to work! Well I really don't know what was the problem, at
work I was trying to apply this formula on my Excel 2003 but it was
not working. But then I tried at home on my Excel 2007 and works
perfectly. Well thank you very much Harlan Grove! I'll probably use
the formula instead of the UDF because when I use any coding in my
workbooks it also being used by other employees and management so it
prompts a security risk due to the excel security settings then there
is a panic. Then I would have to explain why thats happening. Big
headache in short. Anyways Thank You very much Harlan Grove.