CountIf

[ordnance1]

Is it possible to set a CountIf criteria across more than 1 worksheet? I
want to find all occurances of the word John across all 5 worksheets.

[T. Valko]

Not directly but you can use it in combination with other functions.

If your sheet names are unique and don't follow any sort of sequential
naming convention then you have to list the sheet names in a range of cells.
Let's assume you list the sheet names in the range J1:J5.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&J1:J5&"'!A:A"),"John"))

That will do a COUNTIF of column A for the sheets listed in J1:J5 for the
string John.

If your sheet names follow some sort of sequential naming convention (
Week1, Week2, Week3 etc.) then you can build this into the formula and not
have to make a list of the sheet names.

You can always put a separate COUNITF formula on each individual sheet in
the same cell then do a SUM:

=COUNTIF(A:A,"John")

=SUM(Sheet1:Sheet5!B1)

--
Biff
Microsoft Excel MVP

"ordnance1" <ordn...@discussions.microsoft.com> wrote in message
news:C27BECA7-A914-46B4...@microsoft.com...

[Ron Rosenfeld]

Download and install Laurent Longre's free morefunc.xll add-in and use the
COUNTIF.3D function.

1. This does not seem to work in Excel 2007, but works in earlier versions.
2. The usual download site is intermittently non-functional; you may have to
do a google search to find a good site.
--ron