countif help

[Neil22]

I have a workbook with 28 worksheets. One worksheet is a summary of th
other 27. When cell D5 has a quantity greater than 0 I want it t
count the cell if the quantity is 0 I do not want the cell counted.
have used the following and receive the error value.
=COUNTIF('DAY1AY27'D5">0").

Please assist

Thankyou,

Nei

 

[Paul]

I have a workbook with 28 worksheets. One worksheet is a summary of the
other 27. When cell D5 has a quantity greater than 0 I want it to
count the cell if the quantity is 0 I do not want the cell counted. I
have used the following and receive the error value.
=COUNTIF('DAY1AY27'D5">0").

Unfortunately 3-D references are rather limited as regards where they can be
used. Many functions, including COUNTIF, will not accept them. The following
paragraph is copied from Help:

Guidelines for using 3-D references
· You can use 3-D references to refer to cells on other sheets, to define
names, and to create formulas by using the following functions: SUM,
AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV,
STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.
· 3-D references cannot be used in array formulas.
· 3-D references cannot be used with the intersection operator (a single
space) or in formulas that use implicit intersection.

The first two bullet points, taken together, make what you are trying to do
rather difficult. My best suggestion would be a formula on each of the 28
worksheets (in, say, Z99) such as
=IF(D5>0,1,0)
Then D5 on the summary worksheet can simply sum these:
=SUM(DAY1AY27!Z99)