CountIf across multiple sheets

Hi,

I have a bunch of sheets, in which column A has some fruits, for example. I want to know how many apples there are total, across all the sheets. How do I do that? I know you can't use countif for the whole workbook. But how can I do it using sumproduct? I can't use any macros or external libraries or anything like that.

Sheet Jan2005:
Apple
Apple
Apple
Banana

Sheet Feb006:
Banana
Banana
Plum

Sheet March2007:
Apple
Apple

If you list the sheetnames somewhere.. then you can try something like:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$X$1:$X$10&"'!A:A"),"Apple"))

where X1:X10 on the active sheet has the sheetnames.

You can name the range and use that named range too. And you can replace "Apple" with a cell reference containing the search term.