ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Sum uniques across worksheets with criteria (https://www.excelbanter.com/excel-worksheet-functions/154285-sum-uniques-across-worksheets-criteria.html)

Fin Fang Foom
Sum uniques across worksheets with criteria
 
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?

Fin Fang Foom
Sum uniques across worksheets with criteria
 
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?

Fin Fang Foom
Sum uniques across worksheets with criteria
 
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!

Harlan Grove[_2_]
Sum uniques across worksheets with criteria
 
"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)))



Fin Fang Foom
Sum uniques across worksheets with criteria
 
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

Harlan Grove[_2_]
Sum uniques across worksheets with criteria
 
"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.



Fin Fang Foom
Sum uniques across worksheets with criteria
 
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

Fin Fang Foom
Sum uniques across worksheets with criteria
 
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.


All times are GMT +1. The time now is 10:56 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com