Summing across Worksheets

[Guest]

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

 

[Doug Glancy]

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

 

[Guest]

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?

 

[RagDyer]

Try this:

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

 

[Doug Glancy]

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]

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.

 

[Guest]

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]

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!
=====================================================

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

 

[Lori]

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]

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.