COUNTIF

R

r2d3

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

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
 
G

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
 
Top