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