I like the idea ... BUT ... there's no way to *exclude* the referenced cells
in the main or master sheet from being included in the calculations.

Just have to be very careful when working in those main sheets to insure
that those cells remain empty.
And if there are an extensive amount of calculated cells, this could become
very problematical.

ALSO ... when suggesting the use of XLM functions in defined names, it is
best to make sure that you *prominently* make known this type of caveat:
******************
CAVEAT:

This should be used in XL02 and later.
Earlier version *WILL* crash when *copying* this formula to other WBs.
Can be used in all versions as long as you don't copy to *Other* WBs ...
copying within a sheet is OK.
***********************
However, considering the OP's stipulation that formulas must withstand the
*re-naming* of sheets, I personally would take advantage of this function
and configure my main and/or master sheets to operate in the Column AA - BA
ranges, to insure that there is no possibility of contaminating calculations
by accidentally using cells referenced for computation, for other design
purposes.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------



"Lori" wrote in message
ups.com...

You could choose Insert Name Define and type Sheets which refers
to:

=GET.WORKBOOK(1)

to get a variable list of sheets. And then use Ragdyer's formula with
Sheets instead of H1:H30.

RagDyer wrote:

I seem to be making too many mistakes lately.
Allow me to blame it on old age.

Count() will *not* work if there is an actual 0 in any of the F8's.
Works with an empty cell, not with a zero (0) cell.

You'll *need* a list of your sheets.

Then you can try this:

=SUM(start:end!F8)/SUMPRODUCT(COUNTIF(INDIRECT("'"&H1:H30&"'!F8"),"0 "))

With your list of sheet names in H1 to H30.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Doug Glancy" wrote in message
...
I had tried something similar, only with Countif. Interesting to see
that
Count works, I guess because "start:endF8" is an array?. Does it matter
if
there are more than 30 sheets?

Doug


"RagDyer" wrote in message
...
Try this:

=SUM(start:end!F8)/COUNT(start:end!F8,"0")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"justvree" wrote in message
...
Thank you :)

I added 2 sheets, Start & End, put them where they should go and Hid
them.
They're the same as the others but with blank formulas.

If I =SUM(Start:End!F8) it works. If I count that, it works.
However,
I've
tried

=SUMIF(Start:End!F8,"0",Start:End!F8) & =COUNTIF(Start:End!F8,"0")

And all I get is #Value!

Any suggestions?

"Doug Glancy" wrote:

Vree,

This formula sums across all sheets between, and including, "master"
and
"hidden". It should work if you put the formula in "master" and
create
a
dummy last sheet in the workbook called "hidden". This allows you
to
remove/rename the sheets in between without breaking the formula.

=SUM(master:hidden!E8)

Average and Sum will work as you intend if the non-zero cells are
blank. If
you meant that some will have an actual zero, then the SUM above
will
still
work, but for Average you'll need to exclude the zeros, and I don't
know how
to do that with this format. I also don't know how to treat
negatives
numbers, if that's a possibility.

hth,

Doug

"justvree" wrote in message
...
I have 23 Worksheets in a workbook; all sheets are renamed and each
is
called
a person's name. All content is the same, but for the values in
the
charts.
From Sheet 1 I need to Average and/or Sum Each amount in cell "E8"
on
sheets
4-20, but only if it's 0. I've tried many variations, but none
of
them
are
working.

any suggestions? If an example is provided can you also break
down
the
formula for me so I know where the numbers are coming from? Thank
you for
your help.

Vree