Is there a function that I can use to find the occurence of a certain item
across several worksheets? Apparently the Countif cannot cross worksheet
boundaries. I have thought about just using the countif function on each
sheet and them summing them on a cover sheet, but I have 365 sheets to set
up. Any suggestions would be appreciated.

You don;t say much about the ranges to be searched. If we assume it is
the same range in all sheets, e.g. A1:A100, then:

Use an additional range with all the sheet names, say in K1:K10, and
then:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&K1:K10&"'!A1:A100 "),0))

HTH
Kostis Vezerides



r2d3 wrote:
Is there a function that I can use to find the occurence of a certain item
across several worksheets? Apparently the Countif cannot cross worksheet
boundaries. I have thought about just using the countif function on each
sheet and them summing them on a cover sheet, but I have 365 sheets to set
up. Any suggestions would be appreciated.

Do I understand that I will need to write in the names of all 365 worksheets?

"vezerid" wrote:

You don;t say much about the ranges to be searched. If we assume it is
the same range in all sheets, e.g. A1:A100, then:

Use an additional range with all the sheet names, say in K1:K10, and
then:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&K1:K10&"'!A1:A100 "),0))

HTH
Kostis Vezerides



r2d3 wrote:
Is there a function that I can use to find the occurence of a certain item
across several worksheets? Apparently the Countif cannot cross worksheet
boundaries. I have thought about just using the countif function on each
sheet and them summing them on a cover sheet, but I have 365 sheets to set
up. Any suggestions would be appreciated.

Don't type them in.........macro them in.

Private Sub ListSheets()
'list of sheet names starting at A1
Dim rng As Range
Dim i As Integer
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(i, 0).Value = Sheet.Name
i = i + 1
Next Sheet
End Sub

Insert a new sheet and run the macro to get a list of 365 sheets in A1:A365

Alter vezer's formula to suit.

BTW........365 sheets is getting up there. You should look at a
re-organization.


Gord Dibben MS Excel MVP

On Tue, 7 Nov 2006 10:48:02 -0800, r2d3 wrote:

Do I understand that I will need to write in the names of all 365 worksheets?

"vezerid" wrote:

You don;t say much about the ranges to be searched. If we assume it is
the same range in all sheets, e.g. A1:A100, then:

Use an additional range with all the sheet names, say in K1:K10, and
then:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&K1:K10&"'!A1:A100 "),0))

HTH
Kostis Vezerides



r2d3 wrote:
Is there a function that I can use to find the occurence of a certain item
across several worksheets? Apparently the Countif cannot cross worksheet
boundaries. I have thought about just using the countif function on each
sheet and them summing them on a cover sheet, but I have 365 sheets to set
up. Any suggestions would be appreciated.