COUNTIF across multiple sheets

[J]

I have a workbook that has five sheets - September. . . January. I have a
range of cells (D617) where I need to count the occurrence of specific
text from all sheets . For example:

In D617 sheet September has four cells with "fries", two with "malt" and
two with "shrimp", the balance of cells in the range are empty.

In D617 sheet November has four cells with "malt" and all other range
cells are empty

On a sheet named Total I want to total the occurrence of "fries", "shrimp"
and "malt" on all sheets in the workbook. From the example above the total
for "malt" would show 6. Here is what Excel 97 says is the correct formula:

=COUNTIF(Septemberecember!D517,"malt")

but I keep getting a #VALUE! error. The COUNTIF works fine if I "+" together
multiple COUNTIFs but there is a potential for numerous sheets and I do not
want to have a huge formula.

Any help is appreciated.

 

[Max]

Try a previous suggestion to a similar query?
: http://tinyurl.com/3boz4

 

[JE McGimpsey]

CountIf will not work with 3D ranges like that. One workaround:

In an out-of-the-way location in each data sheet, say, cell Z1, put

=COUNTIF(D517,"malt")

(if you group all the sheets first, you can enter in all of them at the
same time). You can then hide the column if you wish.

Then in your Total sheet, enter

=SUM(Septemberecember!Z1)

 

[J]

CountIf will not work with 3D ranges like that. One workaround:

In an out-of-the-way location in each data sheet, say, cell Z1, put

=COUNTIF(D517,"malt")

(if you group all the sheets first, you can enter in all of them at
the same time). You can then hide the column if you wish.

Then in your Total sheet, enter

=SUM(Septemberecember!Z1)

Thanks for the help. I think the McGimpsey's suggestion is a better one for
how I have the workbook set-up now and adding sheets later.

J