COUNTIF

[Franz Verga]

Yes.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Otto Moehrbach]

Slim
You have to give us more than that. What is your question? HTH Otto

 

[Slim]

Slim
You have to give us more than that. What is your question? HTH Otto



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.

 

[Bob Phillips]

Check my response!

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Slim]

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.

 

[Dave Peterson]

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

 

[Bob Phillips]

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]

Thank you Sir. It works a treat!

Cheers,

Slim.