Here is one way;

Assuming there are not other sheets between the ones referenced in the
formula:
Insert a new sheet before the first; call it FirstSheet
Insert a new sheet after the last; call it LastSheet
Never use these sheets for anything
Change all the YES entries to 1
Formula to use now is =SUM(FirstSheet:LastSheet!B2)
Or in another cell (say Z2) use =IF(b2,"yes",1,0)
This can be done by grouping the sheets and typing just once
Formula to use now is =SUM(FirstSheet:LastSheet!Z2)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"ibvalentine" wrote in message
...
I am counting the number of "Yes" answers on each worksheet of a workbook.
All the yes answers on in cell B2 of each sheet. The only way I could come
up
with to accomplish it is as follows:

=COUNTIF('Resize Row'!B2, "=Yes") + COUNTIF('Copy Formatting'!B2, "=Yes")
+
COUNTIF('Formatting Cells'!B2, "=Yes") + COUNTIF('Basic Chart'!B2, "=Yes")
+
COUNTIF('Modify Chart'!B2, "=Yes") + COUNTIF('Sorting Data'!B2, "=Yes") +
COUNTIF('Filtering Data'!B2, "=Yes") + COUNTIF('Printing Data'!B2, "=Yes")
+
COUNTIF('Find and Replace'!B2, "=Yes") + COUNTIF(AutoFill!B2, "=Yes") +
COUNTIF(Comments!B2, "=Yes") + COUNTIF('Number Formatting'!B2, "=Yes")

I am using v. 2003 so I cannot use the new COUNTIFS function. This works
but
it is rather time consuming and long (I have to add 35 sheets at the end).
Does anyone know of a better way without resorting to VBA?

Thanks.