I'm trying to get this formula to work across worksheets. It suppose
to look in column A for the text "r" and find the uniques vaules in
column A and column B and sum the unique vaules in column B.


=SUM((COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!
A1"),ROW(3:99)-1,),"r")=0)*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!
A1"),,,ROW(3:99)),T(OFFSET(INDIRECT($D$3:$D$4&"!
A1"),ROW(3:99)-1,)))=1)*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!
B1"),,,ROW(3:99)),N(OFFSET(INDIRECT($D$3:$D$4&"!
B1"),ROW(3:99)-1,)))=1)*N(OFFSET(INDIRECT($D$3:$D$4&"!
B1"),ROW(3:99)-1,)))



Here what I have in worksheet(2)

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


Worksheet(3)

(A) (B)
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


The total should be 58

Any suggestions?

On Aug 15, 9:29 am, Fin Fang Foom wrote:
I'm trying to get this formula to work across worksheets. It suppose
to look in column A for the text "r" and find the uniques vaules in
column A and column B and sum the unique vaules in column B.

=SUM((COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!
A1"),ROW(3:99)-1,),"r")=0)*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!
A1"),,,ROW(3:99)),T(OFFSET(INDIRECT($D$3:$D$4&"!
A1"),ROW(3:99)-1,)))=1)*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!
B1"),,,ROW(3:99)),N(OFFSET(INDIRECT($D$3:$D$4&"!
B1"),ROW(3:99)-1,)))=1)*N(OFFSET(INDIRECT($D$3:$D$4&"!
B1"),ROW(3:99)-1,)))

Here what I have in worksheet(2)

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

Worksheet(3)

(A) (B)
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

The total should be 58

Any suggestions?


My fault the correct total should be 73.


Any suggestions?

On Aug 15, 9:31 am, Fin Fang Foom wrote:
On Aug 15, 9:29 am, Fin Fang Foom wrote:



I'm trying to get this formula to work across worksheets. It suppose
to look in column A for the text "r" and find the uniques vaules in
column A and column B and sum the unique vaules in column B.

=SUM((COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!
A1"),ROW(3:99)-1,),"r")=0)*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!
A1"),,,ROW(3:99)),T(OFFSET(INDIRECT($D$3:$D$4&"!
A1"),ROW(3:99)-1,)))=1)*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!
B1"),,,ROW(3:99)),N(OFFSET(INDIRECT($D$3:$D$4&"!
B1"),ROW(3:99)-1,)))=1)*N(OFFSET(INDIRECT($D$3:$D$4&"!
B1"),ROW(3:99)-1,)))

Here what I have in worksheet(2)

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

Worksheet(3)

(A) (B)
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

The total should be 58

Any suggestions?

My fault the correct total should be 73.

Any suggestions?


Bump!

"Fin Fang Foom" wrote...
....
I'm trying to get this formula to work across worksheets. It suppose
to look in column A for the text "r" and find the uniques vaules in
column A and column B and sum the unique vaules in column B.

=SUM((COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!A1" ),
ROW(3:99)-1,),"r")=0)*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!A 1"),,,
ROW(3:99)),T(OFFSET(INDIRECT($D$3:$D$4&"!A1"),R OW(3:99)-1,)))=1)
*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!B1"),,,RO W(3:99)),
N(OFFSET(INDIRECT($D$3:$D$4&"!B1"),ROW(3:99)-1,)))=1)
*N(OFFSET(INDIRECT($D$3:$D$4&"!B1"),ROW(3:99)-1,)))
....
Bump!

Don't bump. You'll get an answer when & if someone who knows how to do this
AND has the time responds.

This is one case where a user-defined function would be the better way to do
it because there are so many volatile functions and relatively complex
indexing. Formula using only built-in functions would recalc VERY SLOWLY.

A possible udf,


Function sumifdist3d( _
crng As Range, _
cv As Variant, _
Optional vrng As Range, _
Optional wslst As Variant _
) As Variant
'---------------------
Dim i As Long, j As Long, w As Variant, sv As New Collection
Dim crngws As Range, vrngws As Range, ws As Worksheet

sumifdist3d = CVErr(xlErrRef) 'common error return

If vrng Is Nothing Then Set vrng = crng

If crng.Rows.Count < vrng.Rows.Count _
Or crng.Columns.Count < vrng.Columns.Count Then Exit Function

If TypeOf wslst Is Range Then wslst = wslst.Value

If IsMissing(wslst) Then _
wslst = Array(Application.Caller.Parent.Name)

If Not IsArray(wslst) Then wslst = Array(wslst)

For Each w In wslst
If Not VarType(w) = vbString Then Exit Function

On Error Resume Next
Set ws = Application.Caller.Parent.Parent.Worksheets(w)
If Err.Number < 0 Then Err.Clear: Exit Function
On Error GoTo 0

Set crngws = ws.Range(crng.Address)
Set vrngws = ws.Range(vrng.Address)

For i = 1 To crngws.Rows.Count
For j = 1 To crngws.Columns.Count
If crngws.Cells(i, j).Value = cv Then
On Error Resume Next
sv.Add _
Item:=CDbl(vrngws.Cells(i, j).Value), _
key:=CStr(vrngws.Cells(i, j).Value) 'key is NECESSARY!
On Error GoTo 0
End If
Next j
Next i

Next w

sumifdist3d = 0
For Each w In sv
sumifdist3d = sumifdist3d + w
Next w

End Function


However, if you insist on formulas using only built-in functions, the only
way you're going to be able to do this involves terms like

N(OFFSET(INDIRECT("'"&T(OFFSET($D$3:$D$4,(ROW(1:19 4)-1)/97,0,1,1))
&"'!B3"),MOD(ROW(1:194)-1,97),0))

which would stack the B3:B99 range from the 2nd worksheet under the same
range from the 1st worksheet, forming a 1D array. The number of nested
function calls is already very near Excel's limit (Excel 2003 & prior). You
could define a name like seq referring to =ROW($1:$194)-1 [note: this is
twice the length of ROW(3:99)], then you could use the MONSTER 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)))

On Aug 15, 11:12 pm, "Harlan Grove" wrote:
"Fin Fang Foom" wrote...
...

I'm trying to get this formula to work across worksheets. It suppose
to look in column A for the text "r" and find the uniques vaules in
column A and column B and sum the unique vaules in column B.

=SUM((COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!A1" ),
ROW(3:99)-1,),"r")=0)*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!A 1"),,,
ROW(3:99)),T(OFFSET(INDIRECT($D$3:$D$4&"!A1"),R OW(3:99)-1,)))=1)
*(COUNTIF(OFFSET(INDIRECT($D$3:$D$4&"!B1"),,,RO W(3:99)),
N(OFFSET(INDIRECT($D$3:$D$4&"!B1"),ROW(3:99)-1,)))=1)
*N(OFFSET(INDIRECT($D$3:$D$4&"!B1"),ROW(3:99)-1,)))
...
Bump!

Don't bump. You'll get an answer when & if someone who knows how to do this
AND has the time responds.

This is one case where a user-defined function would be the better way to do
it because there are so many volatile functions and relatively complex
indexing. Formula using only built-in functions would recalc VERY SLOWLY.

A possible udf,

Function sumifdist3d( _
crng As Range, _
cv As Variant, _
Optional vrng As Range, _
Optional wslst As Variant _
) As Variant
'---------------------
Dim i As Long, j As Long, w As Variant, sv As New Collection
Dim crngws As Range, vrngws As Range, ws As Worksheet

sumifdist3d = CVErr(xlErrRef) 'common error return

If vrng Is Nothing Then Set vrng = crng

If crng.Rows.Count < vrng.Rows.Count _
Or crng.Columns.Count < vrng.Columns.Count Then Exit Function

If TypeOf wslst Is Range Then wslst = wslst.Value

If IsMissing(wslst) Then _
wslst = Array(Application.Caller.Parent.Name)

If Not IsArray(wslst) Then wslst = Array(wslst)

For Each w In wslst
If Not VarType(w) = vbString Then Exit Function

On Error Resume Next
Set ws = Application.Caller.Parent.Parent.Worksheets(w)
If Err.Number < 0 Then Err.Clear: Exit Function
On Error GoTo 0

Set crngws = ws.Range(crng.Address)
Set vrngws = ws.Range(vrng.Address)

For i = 1 To crngws.Rows.Count
For j = 1 To crngws.Columns.Count
If crngws.Cells(i, j).Value = cv Then
On Error Resume Next
sv.Add _
Item:=CDbl(vrngws.Cells(i, j).Value), _
key:=CStr(vrngws.Cells(i, j).Value) 'key is NECESSARY!
On Error GoTo 0
End If
Next j
Next i

Next w

sumifdist3d = 0
For Each w In sv
sumifdist3d = sumifdist3d + w
Next w

End Function

However, if you insist on formulas using only built-in functions, the only
way you're going to be able to do this involves terms like

N(OFFSET(INDIRECT("'"&T(OFFSET($D$3:$D$4,(ROW(1:19 4)-1)/97,0,1,1))
&"'!B3"),MOD(ROW(1:194)-1,97),0))

which would stack the B3:B99 range from the 2nd worksheet under the same
range from the 1st worksheet, forming a 1D array. The number of nested
function calls is already very near Excel's limit (Excel 2003 & prior). You
could define a name like seq referring to =ROW($1:$194)-1 [note: this is
twice the length of ROW(3:99)], then you could use the MONSTER 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)))

Thank You for responding Harlan Grove.

Nice UDF function. The mega formula you provided I'm getting the wrong
total. I'm getting 9 it should be 73.

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.

When you get a chance let me know, If not I'll use you UDF.

Thanks Harlan Grove

"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.