Looking for a better solution

I

ibvalentine

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

Peo Sjoblom

If you could put all sheet names in let's say I1:I35 than you could use this
formula


=SUMPRODUCT(COUNTIF(INDIRECT("'"&I1:I35&"'!B2"),"Yes"))

So a one time effort and put the sheet names in a range, all the sheets
that are involved. After that it's a breeze


--


Regards,


Peo Sjoblom
 
T

T. Valko

List your sheet names in a range of cells. Assume that range is A1:A10. Give
this range a defined name. Select the range A1:A10. In the name box (that
little space directly above the column A header) type a name for the range
then hit enter. We'll use SheetNames for this example. Then use a formula
like this:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!B2"),"Yes"))
 
R

Ron Rosenfeld

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 from
http://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
 
B

Bernard Liengme

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
 
P

Pete_UK

In a cell in each of those sheets (eg Z1), you could put this formula:

=IF(UPPER(B2)="YES",1,0)

Then on your summary sheet you could have:

=SUM('Resize Row':'Number Formatting'!Z1)

This assumes that all those sheet tabs appear next to each other, and
that there are no other sheets within the "sandwich" formed by 'Resize
Row' and 'Number Formatting'. You could just create two blank sheets
and call one of them "first" and the other one "last" and position
them so that they encompass the other sheets (but not the summary
sheet), and then you could have this on your summary sheet:

=SUM(first:last!Z1)

You can drag sheets into or out of the sandwich formed by the sheets
first and last to model different effects.

Hope this helps.

Pete
 
K

Ken Johnson

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.

Use =IF(B2="Yes",1,0) in the same free cell on each of your sheets, eg
C2, then use...

=SUM('Resize Row:Number Formatting'!C2)

I have assumed that with the above sheets Resize Row Sheet's tab is
the left-most tab and Number Formatting Sheet's tab is the right-most
tab.

Ken Johnson

Ken Johnson
 
R

Rick Rothstein

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

ibvalentine

I am very appreciative of everyone's input. I put Mr. Rothstein's very clear
and easy instructions to use and the custom function works like a charm!

Thanks to everyone!
 
S

Spiky

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?
 
R

Ron Rosenfeld

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

Have you tried to use COUNTIF.3D?

On my version (5.06), which I have redownloaded to be sure I didn't miss
anything, COUNTIF.3D, although documented in HELP, does not exist.
--ron
 
S

Spiky

Have you tried to use COUNTIF.3D?
On my version (5.06), which I have redownloaded to be sure I didn't miss
anything, COUNTIF.3D, although documented in HELP, does not exist.
--ron

Weird. It's there for me. Also 5.06, downloaded sometime this last
spring, IIRC. I tried it quick and it works. I put this on 2 sheets:
Start
Start
Stop
Stop
Start
Stop
Stop
Stop
Start

=COUNTIF.3D(Sheet1:Sheet2!A1:A9,"Stop")
Returns "10".

I don't know what to tell you. Corrupted download?
 
T

T. Valko

I'm not sure which version I have. If I check the properties of Morefunc.xll
there is no version info.

Whichever version I have, COUNTIF.3D exists and does work.
 
R

Ron Rosenfeld

Weird. It's there for me. Also 5.06, downloaded sometime this last
spring, IIRC. I tried it quick and it works. I put this on 2 sheets:
Start
Start
Stop
Stop
Start
Stop
Stop
Stop
Start

=COUNTIF.3D(Sheet1:Sheet2!A1:A9,"Stop")
Returns "10".

I don't know what to tell you. Corrupted download?

Well, I will try again.
--ron
 
R

Ron Rosenfeld

I'm not sure which version I have. If I check the properties of Morefunc.xll
there is no version info.

Whichever version I have, COUNTIF.3D exists and does work.

Not sure what's going on.

I uninstalled morefunc, then reinstalled 5.06. I couldn't download it again as
I kept getting errors when trying to access the web site.

COUNTIF.3D is in the function list (as it was before). But when I paste it
into a cell and try to use it, I get a #NAME error.

In addition, the function wizard only shows a single line for an argument.

Are you and Spiky running Excel 2007? If not, I wonder if that could be the
issue.
--ron
 
T

T. Valko

I originally tested in Excel 2002.

I just tested in Excel 2007 and now I see what you mean.

Type in a formula using COUNTIF.3D and the result is #NAME?.

On the Formulas tab>Insert function>Category>Morefunc

COUNTIF3.D is not listed in the available functions.

I also ran across something several months ago while using Excel 2007 and
Morefunc and posted about it.

Again, on the Formulas tab>Morefunc group>Function

When you select a function (any function) the user form doesn't open. I
never use this method so it's not a big deal to me, but....
 
R

Ron Rosenfeld

When you select a function (any function) the user form doesn't open. I
never use this method so it's not a big deal to me, but....

That's not the case for me. A "user form" opens, and for several I tested
other than COUNTIF.3D, it's the same form as opens when using the regular
function bar.

There are a few other functions that supposedly do not work in 2007, but I
don't recall what they are.

BTW, do you know if anything has happened to Longre? I've seen nothing from
him for quite some time.
--ron
 
S

Spiky

I am still on xl2003, so that must be it. There have been several
threads here about code that "broke" in xl2007. This is part of the
reason we haven't upgraded. (although I think they are starting to,
now)

I managed to download from the source website earlier this year when I
first heard about morefunc. But since then it has been up and down. I
believe it is held separately at download.com, so that is a better
place to get it.

No idea about the author. Maybe he upgraded to Vista and 2007 this
summer and hasn't gotten his computer back to functional, yet. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top