Sum uniques across worksheets with criteria

F

Fin Fang Foom

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?
 
F

Fin Fang Foom

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?
Click to expand...


My fault the correct total should be 73.


Any suggestions?
 
H

Harlan Grove

Fin Fang Foom said:
....
Bump!
Click to expand...

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:194)-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(OFFSET($D$3:$D$4,
seq/97,0,1,1))&"'!B3"),MOD(seq,97),0)),T(OFFSET(INDIRECT("'"
&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(INDIRECT("'"
&T(OFFSET($D$3:$D$4,seq/97,0,1,1))&"'!B3"),MOD(seq,97),0)))
 
F

Fin Fang Foom

...
Bump!
Click to expand...

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:194)-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(OFFSET($D$3:$D$4,
seq/97,0,1,1))&"'!B3"),MOD(seq,97),0)),T(OFFSET(INDIRECT("'"
&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(INDIRECT("'"
&T(OFFSET($D$3:$D$4,seq/97,0,1,1))&"'!B3"),MOD(seq,97),0)))
Click to expand...

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
 
H

Harlan Grove

Fin Fang Foom said:
. . . The mega formula you provided I'm getting the wrong
total. I'm getting 9 it should be 73.
Click to expand...
....

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(OFFSET($D$3:$D$4,
seq/97,0,1,1))&"'!B3"),MOD(seq,97),0)),T(OFFSET(INDIRECT("'"
&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(INDIRECT("'"
&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.
Click to expand...

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

Fin Fang Foom

...

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(OFFSET($D$3:$D$4,
seq/97,0,1,1))&"'!B3"),MOD(seq,97),0)),T(OFFSET(INDIRECT("'"
&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(INDIRECT("'"
&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.








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 -
Click to expand...


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
 
F

Fin Fang Foom

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
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding dynamic maxima 9
sum by rows in Pivot 1
Filtering data 2
Sum of Variable Cells in a fixed Row 3
Formula help needed 1
Sumproduct? 3
Hlookup 3 criteria?? 0
Excel VBA macro, search - accumulate - delete original 0

Top