On Nov 3, 4:54*pm, Ron Rosenfeld wrote:
On Mon, 3 Nov 2008 14:17:01 -0800, ibvalentine



wrote:
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.

Download and install Longre's free morefunc.xll add-in fromhttp://xcell05..free.fr

Then use the THREED function to create an array from the 3D reference

If you place Sheets named Start and End at the beginning and end of your range,
you could use a formula like:

=SUMPRODUCT(N(THREED(Start:End!B2)="Yes"))
--ron

If you are going to use morefunc, why not just use COUNTIF.3D?