COUNTIF

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

r2d3 - 31 Oct 2006 18:52 GMT

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.

Reply to this Message

vezerid - 31 Oct 2006 19:53 GMT

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

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

Reply to this Message

r2d3 - 07 Nov 2006 18:48 GMT

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

> 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:
[quoted text clipped - 12 lines]
> > sheet and them summing them on a cover sheet, but I have 365 sheets to set
> > up. Any suggestions would be appreciated.

Reply to this Message

Gord Dibben - 07 Nov 2006 20:14 GMT

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

>Do I understand that I will need to write in the names of all 365 worksheets?
>
[quoted text clipped - 14 lines]
>> > sheet and them summing them on a cover sheet, but I have 365 sheets to set
>> > up. Any suggestions would be appreciated.

Reply to this Message