If you are able to make use of VBA code, you might try the following User
Defined Function (UDF). Press Alt+F11 to go to the VBA editor, click
Insert/Module from its menu bar and copy/paste the following code into the
code window that appears...

Public Function Yesses()
Dim WS As Worksheet
Application.Volatile
For Each WS In Worksheets
If UCase(WS.Range("B2").Value) = "YES" Then Yesses = Yesses + 1
Next
End Function

Once you have done this, you can use this function like any other built-in
function. For example, put =Yesses() in a cell and it should provide you
with the count you asked for.

--
Rick (MVP - Excel)


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