COUNTIF

[Guest]

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.

 

[vezerid]

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

 

[Guest]

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

 

[Gord Dibben]

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