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