Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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"),"Y es")) 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 "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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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")) -- Biff Microsoft Excel MVP "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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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 On Nov 3, 10:17*pm, 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
On Nov 4, 9:17*am, 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. 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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! "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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
On Tue, 4 Nov 2008 07:53:55 -0800 (PST), Spiky wrote:
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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. -- Biff Microsoft Excel MVP "Ron Rosenfeld" wrote in message ... On Tue, 4 Nov 2008 07:53:55 -0800 (PST), Spiky wrote: 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
They have a UDF that gives the version. MFVERSION or something like
that. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
Ok, got it:
=MRFVERSION() 5.06 -- Biff Microsoft Excel MVP "Spiky" wrote in message ... They have a UDF that gives the version. MFVERSION or something like that. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
On Tue, 4 Nov 2008 13:20:26 -0800 (PST), Spiky wrote:
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 |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
On Tue, 4 Nov 2008 16:30:58 -0500, "T. Valko" wrote:
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 |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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 tabInsert functionCategoryMorefunc 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 tabMorefunc groupFunction 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.... -- Biff Microsoft Excel MVP "Ron Rosenfeld" wrote in message ... On Tue, 4 Nov 2008 16:30:58 -0500, "T. Valko" wrote: 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 |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
On Tue, 4 Nov 2008 21:08:50 -0500, "T. Valko" wrote:
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 |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
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. :-) |
#21
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a better solution
BTW, do you know if anything has happened to Longre?
I've seen nothing from him for quite some time. No, I don't have any info on that. -- Biff Microsoft Excel MVP "Ron Rosenfeld" wrote in message ... On Tue, 4 Nov 2008 21:08:50 -0500, "T. Valko" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
solution wants | Excel Discussion (Misc queries) | |||
Web Solution | Excel Discussion (Misc queries) | |||
looking for a solution | Excel Worksheet Functions | |||
My solution | Excel Discussion (Misc queries) | |||
Solution please | Excel Discussion (Misc queries) |