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

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

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

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

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

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

Hi!

Thank you for your help. I tried using your formula,and it didn't work. I
cannot list all of the names of the worksheets as they're going to change and
I may not be around to fix the code. So, I'm trying to make it work
regardless of the names of the sheets. Am I going to have to do a macro and
count each column type individually? This should be something simple, but
it's turning complicated :( Can you provide any other insight?

Again... here is the senario...

There are now 25 sheets. Sheet 1-3 are summary sheets. 4 and 25 are the
Start and End sheets. 4-24 have the data and the names of these sheets will
change.

For Each Row (E8 - K8: E29 - K29) for each sheet, it needs to be added. I
want all the E8's added, all the F8's added etc... but I need them averaged.
I can count them, I can sum them. But I only want them counted if it's
Greater than Zero. All my countif's sumif's etc aren't working. For
instance, there are 20 people (one per sheet) but not all of them have states
or figures for each cell. If the cell for that person is Zero, I don't want
it averaged. I don't want it counted. How can I keep that out of the count?

Thanks... Vicky

"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

I would guess that your simplest approach would be to add formulas to each
individual sheet, in an out-of-the-way location.
Since the set-up of all the sheets are identical, this can be easily
accomplished "en masse" by grouping your sheets.

Then, your master sheet can easily reference (and calculate) these
individual formula cells.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Just Vree" <Just wrote in message
...
Hi!

Thank you for your help. I tried using your formula,and it didn't work. I
cannot list all of the names of the worksheets as they're going to change
and
I may not be around to fix the code. So, I'm trying to make it work
regardless of the names of the sheets. Am I going to have to do a macro and
count each column type individually? This should be something simple, but
it's turning complicated :( Can you provide any other insight?

Again... here is the senario...

There are now 25 sheets. Sheet 1-3 are summary sheets. 4 and 25 are the
Start and End sheets. 4-24 have the data and the names of these sheets will
change.

For Each Row (E8 - K8: E29 - K29) for each sheet, it needs to be added. I
want all the E8's added, all the F8's added etc... but I need them averaged.
I can count them, I can sum them. But I only want them counted if it's
Greater than Zero. All my countif's sumif's etc aren't working. For
instance, there are 20 people (one per sheet) but not all of them have
states
or figures for each cell. If the cell for that person is Zero, I don't want
it averaged. I don't want it counted. How can I keep that out of the
count?

Thanks... Vicky

"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

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

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