| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF
|
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
"Slim" ha scritto nel messaggio
... Yes. -- Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Slim
You have to give us more than that. What is your question? HTH Otto "Slim" wrote in message ... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
"Otto Moehrbach" wrote: Slim You have to give us more than that. What is your question? HTH Otto "Slim" wrote in message ... I am trying to use the COUNTIF function over a number of worksheets in a spreadsheet to counht various values in a given cell on each sheet. However, I just get a #VALUE error. An example of the formula I am trying is =COUNTIF('Blank Sheet:Blank Sheet 2'!D2, "0") Any help much appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Check my response!
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Slim" wrote in message ... "Otto Moehrbach" wrote: Slim You have to give us more than that. What is your question? HTH Otto "Slim" wrote in message ... I am trying to use the COUNTIF function over a number of worksheets in a spreadsheet to counht various values in a given cell on each sheet. However, I just get a #VALUE error. An example of the formula I am trying is =COUNTIF('Blank Sheet:Blank Sheet 2'!D2, "0") Any help much appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Thanks Bob, but it is not quite what I need. I obviously need to supply more
details - What I have is a spreadsheet containing over 120 worksheets. The last sheet is a template sheet used to create new case sheets from. The penultimate sheet and the second sheet are 'blank' sheets used to make some data collation formulae work properly. (I actually have half a dozen of these spreadsheets, each created from a master template. Hence the reason for using blank sheets so that I did not get a load of errors when there were no case sheets in the empty files). The first sheet in the file is a data sheet that each of the 120+ case sheets references for various values. Each case sheet perfroms various calculations and then references a lookup table on the data sheet to get an integer value (depending on the value of the calculations), ranging from 0 to 40 or a dummy value -98. The integer value is stored on each case sheet in cell D2. The two blank sheets store the dummy value -99 in cell D2. What I am trying to do is count up how many case sheets have the value 0 in cell D2, how many have the value 1 in cell D2, etc, and display this data on the first sheet - the original data sheet. Hence the countif formulae =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=0") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=1") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=2") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=3") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=4") etc, etc... Unfortunately, all I get is the #VALUE! error when I use any of the basic COUNTIF formulae listed above. I have been able to use the COUNT function on a specific cell in each of the case sheets between the two blank sheets, but not the COUNTIF function. Hopefully this all makes sense and I have given you enough information to help me... Cheers, Slim. "Bob Phillips" wrote: Check my response! -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Slim" wrote in message ... "Otto Moehrbach" wrote: Slim You have to give us more than that. What is your question? HTH Otto "Slim" wrote in message ... I am trying to use the COUNTIF function over a number of worksheets in a spreadsheet to counht various values in a given cell on each sheet. However, I just get a #VALUE error. An example of the formula I am trying is =COUNTIF('Blank Sheet:Blank Sheet 2'!D2, "0") Any help much appreciated. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
You may be able to use some of the 3D userdefined functions that Myrna Larson
and David Hager wrote:. At John Walkenbach's site: http://j-walk.com/ss/excel/eee/eee003.txt (Countif3d/sumif3d/sumproduct3d) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Slim wrote: Thanks Bob, but it is not quite what I need. I obviously need to supply more details - What I have is a spreadsheet containing over 120 worksheets. The last sheet is a template sheet used to create new case sheets from. The penultimate sheet and the second sheet are 'blank' sheets used to make some data collation formulae work properly. (I actually have half a dozen of these spreadsheets, each created from a master template. Hence the reason for using blank sheets so that I did not get a load of errors when there were no case sheets in the empty files). The first sheet in the file is a data sheet that each of the 120+ case sheets references for various values. Each case sheet perfroms various calculations and then references a lookup table on the data sheet to get an integer value (depending on the value of the calculations), ranging from 0 to 40 or a dummy value -98. The integer value is stored on each case sheet in cell D2. The two blank sheets store the dummy value -99 in cell D2. What I am trying to do is count up how many case sheets have the value 0 in cell D2, how many have the value 1 in cell D2, etc, and display this data on the first sheet - the original data sheet. Hence the countif formulae =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=0") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=1") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=2") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=3") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=4") etc, etc... Unfortunately, all I get is the #VALUE! error when I use any of the basic COUNTIF formulae listed above. I have been able to use the COUNT function on a specific cell in each of the case sheets between the two blank sheets, but not the COUNTIF function. Hopefully this all makes sense and I have given you enough information to help me... Cheers, Slim. "Bob Phillips" wrote: Check my response! -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Slim" wrote in message ... "Otto Moehrbach" wrote: Slim You have to give us more than that. What is your question? HTH Otto "Slim" wrote in message ... I am trying to use the COUNTIF function over a number of worksheets in a spreadsheet to counht various values in a given cell on each sheet. However, I just get a #VALUE error. An example of the formula I am trying is =COUNTIF('Blank Sheet:Blank Sheet 2'!D2, "0") Any help much appreciated. -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
You can do it by putting all the sheet names to add in a list in say
M1:M100, and then use =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M100&"'!D2"),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Slim" wrote in message ... Thanks Bob, but it is not quite what I need. I obviously need to supply more details - What I have is a spreadsheet containing over 120 worksheets. The last sheet is a template sheet used to create new case sheets from. The penultimate sheet and the second sheet are 'blank' sheets used to make some data collation formulae work properly. (I actually have half a dozen of these spreadsheets, each created from a master template. Hence the reason for using blank sheets so that I did not get a load of errors when there were no case sheets in the empty files). The first sheet in the file is a data sheet that each of the 120+ case sheets references for various values. Each case sheet perfroms various calculations and then references a lookup table on the data sheet to get an integer value (depending on the value of the calculations), ranging from 0 to 40 or a dummy value -98. The integer value is stored on each case sheet in cell D2. The two blank sheets store the dummy value -99 in cell D2. What I am trying to do is count up how many case sheets have the value 0 in cell D2, how many have the value 1 in cell D2, etc, and display this data on the first sheet - the original data sheet. Hence the countif formulae =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=0") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=1") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=2") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=3") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=4") etc, etc... Unfortunately, all I get is the #VALUE! error when I use any of the basic COUNTIF formulae listed above. I have been able to use the COUNT function on a specific cell in each of the case sheets between the two blank sheets, but not the COUNTIF function. Hopefully this all makes sense and I have given you enough information to help me... Cheers, Slim. "Bob Phillips" wrote: Check my response! -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Slim" wrote in message ... "Otto Moehrbach" wrote: Slim You have to give us more than that. What is your question? HTH Otto "Slim" wrote in message ... I am trying to use the COUNTIF function over a number of worksheets in a spreadsheet to counht various values in a given cell on each sheet. However, I just get a #VALUE error. An example of the formula I am trying is =COUNTIF('Blank Sheet:Blank Sheet 2'!D2, "0") Any help much appreciated. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF
Thank you Sir. It works a treat!
Cheers, Slim. "Bob Phillips" wrote: You can do it by putting all the sheet names to add in a list in say M1:M100, and then use =SUMPRODUCT(COUNTIF(INDIRECT("'"&M1:M100&"'!D2"),0 )) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Slim" wrote in message ... Thanks Bob, but it is not quite what I need. I obviously need to supply more details - What I have is a spreadsheet containing over 120 worksheets. The last sheet is a template sheet used to create new case sheets from. The penultimate sheet and the second sheet are 'blank' sheets used to make some data collation formulae work properly. (I actually have half a dozen of these spreadsheets, each created from a master template. Hence the reason for using blank sheets so that I did not get a load of errors when there were no case sheets in the empty files). The first sheet in the file is a data sheet that each of the 120+ case sheets references for various values. Each case sheet perfroms various calculations and then references a lookup table on the data sheet to get an integer value (depending on the value of the calculations), ranging from 0 to 40 or a dummy value -98. The integer value is stored on each case sheet in cell D2. The two blank sheets store the dummy value -99 in cell D2. What I am trying to do is count up how many case sheets have the value 0 in cell D2, how many have the value 1 in cell D2, etc, and display this data on the first sheet - the original data sheet. Hence the countif formulae =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=0") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=1") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=2") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=3") =COUNTIF('Blank Sheet:Blank Sheet 2'!D2,"=4") etc, etc... Unfortunately, all I get is the #VALUE! error when I use any of the basic COUNTIF formulae listed above. I have been able to use the COUNT function on a specific cell in each of the case sheets between the two blank sheets, but not the COUNTIF function. Hopefully this all makes sense and I have given you enough information to help me... Cheers, Slim. "Bob Phillips" wrote: Check my response! -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Slim" wrote in message ... "Otto Moehrbach" wrote: Slim You have to give us more than that. What is your question? HTH Otto "Slim" wrote in message ... I am trying to use the COUNTIF function over a number of worksheets in a spreadsheet to counht various values in a given cell on each sheet. However, I just get a #VALUE error. An example of the formula I am trying is =COUNTIF('Blank Sheet:Blank Sheet 2'!D2, "0") Any help much appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumproduct vs. countif | Excel Discussion (Misc queries) | |||
| Countif Formula /Sort Bug??? | Excel Discussion (Misc queries) | |||
| COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
| Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
| Countif - Countif | Excel Worksheet Functions | |||
Linear Mode
