count unique entries across worksheets
 

I have a workbook with multiple worksheets. Each worksheet has a number of
text entries in a column. Some of the entries are repeated on more than one
worksheet. Is there a formula I can construct that will return the number of
unique text entries across the whole workbook instead of just one sheet?

Example:
Sheet 1
A
B
C
D

Sheet 2
B
E
F
G

Sheet 3
D
E
I
J

Unique entries = 9

What formula can I use to generate that '9'? Thanks.

count unique entries across worksheets
 

one of many passiblities would be
=countif('Sheet 1'!,A:A,"A")+countif('Sheet 2'!,A:A,"A")+countif('Sheet
3'!,A:A,"A")+

"globetrotter" wrote:

I have a workbook with multiple worksheets. Each worksheet has a number of
text entries in a column. Some of the entries are repeated on more than one
worksheet. Is there a formula I can construct that will return the number of
unique text entries across the whole workbook instead of just one sheet?

Example:
Sheet 1
A
B
C
D

Sheet 2
B
E
F
G

Sheet 3
D
E
I
J

Unique entries = 9

What formula can I use to generate that '9'? Thanks.

count unique entries across worksheets
 

also if you use seach for countif and 3d you find a good responce from Bob
Phillips to the question "Countif across multiple sheets" from gizmo63 in
april 06

"=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C8"),"v alue"))

where C1:C3 is a range housing the relevant sheetnames in
separate cells."

I like his formula



"bj" wrote:

one of many passiblities would be
=countif('Sheet 1'!,A:A,"A")+countif('Sheet 2'!,A:A,"A")+countif('Sheet
3'!,A:A,"A")+

"globetrotter" wrote:

I have a workbook with multiple worksheets. Each worksheet has a number of
text entries in a column. Some of the entries are repeated on more than one
worksheet. Is there a formula I can construct that will return the number of
unique text entries across the whole workbook instead of just one sheet?

Example:
Sheet 1
A
B
C
D

Sheet 2
B
E
F
G

Sheet 3
D
E
I
J

Unique entries = 9

What formula can I use to generate that '9'? Thanks.

count unique entries across worksheets
 

bj,

I'll check out what you posted, but doesn't 'countif' only recognize cells
with numbers? Mine are text entries.

"bj" wrote:

also if you use seach for countif and 3d you find a good responce from Bob
Phillips to the question "Countif across multiple sheets" from gizmo63 in
april 06

"=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C8"),"v alue"))

where C1:C3 is a range housing the relevant sheetnames in
separate cells."

I like his formula



"bj" wrote:

one of many passiblities would be
=countif('Sheet 1'!,A:A,"A")+countif('Sheet 2'!,A:A,"A")+countif('Sheet
3'!,A:A,"A")+

"globetrotter" wrote:

I have a workbook with multiple worksheets. Each worksheet has a number of
text entries in a column. Some of the entries are repeated on more than one
worksheet. Is there a formula I can construct that will return the number of
unique text entries across the whole workbook instead of just one sheet?

Example:
Sheet 1
A
B
C
D

Sheet 2
B
E
F
G

Sheet 3
D
E
I
J

Unique entries = 9

What formula can I use to generate that '9'? Thanks.

count unique entries across worksheets
 

countif works with text

"globetrotter" wrote:

bj,

I'll check out what you posted, but doesn't 'countif' only recognize cells
with numbers? Mine are text entries.

"bj" wrote:

also if you use seach for countif and 3d you find a good responce from Bob
Phillips to the question "Countif across multiple sheets" from gizmo63 in
april 06

"=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C8"),"v alue"))

where C1:C3 is a range housing the relevant sheetnames in
separate cells."

I like his formula



"bj" wrote:

one of many passiblities would be
=countif('Sheet 1'!,A:A,"A")+countif('Sheet 2'!,A:A,"A")+countif('Sheet
3'!,A:A,"A")+

"globetrotter" wrote:

I have a workbook with multiple worksheets. Each worksheet has a number of
text entries in a column. Some of the entries are repeated on more than one
worksheet. Is there a formula I can construct that will return the number of
unique text entries across the whole workbook instead of just one sheet?

Example:
Sheet 1
A
B
C
D

Sheet 2
B
E
F
G

Sheet 3
D
E
I
J

Unique entries = 9

What formula can I use to generate that '9'? Thanks.

count unique entries across worksheets
 

bj,

I was able to answer my own previous question about number vs text entries.
Looking at the formula you gave and the one you referenced by Bob Phillips,
however, neither seem to count only the unique entries. They seem to count
all text entries instead. I'm not experienced with 3-D references, so I
can't easily decipher what Bob wrote. What am I missing?


"bj" wrote:

also if you use seach for countif and 3d you find a good responce from Bob
Phillips to the question "Countif across multiple sheets" from gizmo63 in
april 06

"=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C8"),"v alue"))

where C1:C3 is a range housing the relevant sheetnames in
separate cells."

I like his formula



"bj" wrote:

one of many passiblities would be
=countif('Sheet 1'!,A:A,"A")+countif('Sheet 2'!,A:A,"A")+countif('Sheet
3'!,A:A,"A")+

"globetrotter" wrote:

I have a workbook with multiple worksheets. Each worksheet has a number of
text entries in a column. Some of the entries are repeated on more than one
worksheet. Is there a formula I can construct that will return the number of
unique text entries across the whole workbook instead of just one sheet?

Example:
Sheet 1
A
B
C
D

Sheet 2
B
E
F
G

Sheet 3
D
E
I
J

Unique entries = 9

What formula can I use to generate that '9'? Thanks.

count unique entries across worksheets
 

I diddn't read your original posting correctly
I appologize

the simplest way may be to use a helper sheet where you use a formula or
just copy all of the signifcant data to the sheet.

advanced filter or a multiple of formulas can be used to get the unique list
count from this combined list


"globetrotter" wrote:

bj,

I was able to answer my own previous question about number vs text entries.
Looking at the formula you gave and the one you referenced by Bob Phillips,
however, neither seem to count only the unique entries. They seem to count
all text entries instead. I'm not experienced with 3-D references, so I
can't easily decipher what Bob wrote. What am I missing?


"bj" wrote:

also if you use seach for countif and 3d you find a good responce from Bob
Phillips to the question "Countif across multiple sheets" from gizmo63 in
april 06

"=SUMPRODUCT(COUNTIF(INDIRECT("'"&C1:C3&"'!C8"),"v alue"))

where C1:C3 is a range housing the relevant sheetnames in
separate cells."

I like his formula



"bj" wrote:

one of many passiblities would be
=countif('Sheet 1'!,A:A,"A")+countif('Sheet 2'!,A:A,"A")+countif('Sheet
3'!,A:A,"A")+

"globetrotter" wrote:

I have a workbook with multiple worksheets. Each worksheet has a number of
text entries in a column. Some of the entries are repeated on more than one
worksheet. Is there a formula I can construct that will return the number of
unique text entries across the whole workbook instead of just one sheet?

Example:
Sheet 1
A
B
C
D

Sheet 2
B
E
F
G

Sheet 3
D
E
I
J

Unique entries = 9

What formula can I use to generate that '9'? Thanks.