Indirect Sum

[Joe L.]

I have several worksheets where I want to sum various portion of a column of
numbers (Columns W1 thru W50). On each worksheet I have dozens of "summing'
formulas, each summing various portions of Column W: e.g. - W1:W6; W7:W14;
W15:W24; W25:W35; W36:W50. Each of the several worksheets has the same
general format of summing formulas EXCEPT the portions of the columns summed
change from worksheet to worksheet. For example, worksheet 1 might sum W1:W6
while worksheet 2 sums W1:W9 and worksheet 3 sums W1:W15, etc.
What I'm trying to do is to Indirectly reference the formulas by creating on
each worksheet a table which indirectly specifies which cells to sum. For
example on worksheet 1, the formula W1:W6 will actually specified by INDIRECT
A9:A10. The data in B9 will specify "W1", and the data in "B10" will specify
"W6". This way, I can use the same setup on every worksheet and just change
the data in column B whenever I want to vary the columns summed.

 

[Pete_UK]

You didn't actually ask a question, but I think this is what you want:

=SUM(INDIRECT(A9&":"&A10))

However, if your sum ranges vary because you have different amounts of
data for some other criteria, you might be able to use SUMIF over your
complete range W1:W50. Perhaps you can explain why your ranges have
different lengths on different sheets?

Hope this helps.

Pete

 

[Joe L.]

Pete:
Thanks for the response! I was afraid I didn't make myself clear enough. Let
me try again without being so abstract.
I'm analysing a huge amount of data that was recorded at various times
(Weekday Mornings, Weekday Afternoons, Weekday Evenings, Weekend Mornings,
Weekend Afternoons & Weekend Evenings) and at different Locations (Park 1,
Park 2, Park 3, etc. thru Park 25). Each Location or Park has a separate
Worksheet dedicated to the data recorded at that Park. All the data is sorted
by the above 6 time periods. The Column WI thru W50 I previously referred to
is just one of several Colums that contain different pieces of recorded data.
I need to "sum" the data in Column W (and the other Columns) according to the
6 time periods.
The problem lies in the fact that there were not equal numbers of recorded
data for each time period. Thus, for the data in Column W at Park 1, the
Weekday Morning (WD-M) period may be from W1 thru W10, and the WD-A may be
from W11 thru W19. HOWEVER, at Park 2, the WD-M data may be from W1 thru W7,
and the WD-A data may be from W8 thru W22. Etc, Etc. Since there are multiple
summing formulas on each worksheet, it is a huge amount of work to enter all
the time period ranges into each formula. My IDEA was to create a Table with
INDIRECT referencing. Then, for Park 1, to sum Cells W1 to W10, instead of
entering the formula =Sum(w1:w10), I would enter a formula similar to
=sum(INDIRECT(a101:a102)). [I know this is not the actual formula I want to
use] These "A" cells are just arbitrarily chosen cells. But in cells B101 and
B102, I would put the actual cell range I wanted to sum for the Weekday
Morning period (W1 and W10). I would also fill in the cells that
corresponded with the other 5 time periods (e.g. B102/B103 would have
reference cells W11/W19 for the WD-A period; B104/B105 would have W20 to W29
for the WD-E period; etc, etc.)
This way, I would only have to enter the indirect summing formulas for the
first worksheet. Then i could copy all these formulas to the other 25
worksheets for the 25 different Parks, and then just change the reference in
Cells B101 thru B112 to the correct range for each time period for EACH
worksheet. This would automatically change all the formulas on that worksheet
to the correct ranges.
My question - finally - is what is the exact structure of this Indirect Sum
formula that I'm trying to create? The one you gave me with your initial
response would work if there are "numbers" in Cells B101 thu B112. What I
want is a formula that will work if Cells B101 thru B112 contain a Cell
reference such as W1 or W10 or W11, etc.
Sorry the explanation is so long but I hope it is a bit clearer. Is it
possible to do what I want?
Thanks .... Joe

 

[Glenn]

Pete:
Thanks for the response! I was afraid I didn't make myself clear enough. Let
me try again without being so abstract.
I'm analysing a huge amount of data that was recorded at various times
(Weekday Mornings, Weekday Afternoons, Weekday Evenings, Weekend Mornings,
Weekend Afternoons & Weekend Evenings) and at different Locations (Park 1,
Park 2, Park 3, etc. thru Park 25). Each Location or Park has a separate
Worksheet dedicated to the data recorded at that Park. All the data is sorted
by the above 6 time periods. The Column WI thru W50 I previously referred to
is just one of several Colums that contain different pieces of recorded data.
I need to "sum" the data in Column W (and the other Columns) according to the
6 time periods.
The problem lies in the fact that there were not equal numbers of recorded
data for each time period. Thus, for the data in Column W at Park 1, the
Weekday Morning (WD-M) period may be from W1 thru W10, and the WD-A may be
from W11 thru W19. HOWEVER, at Park 2, the WD-M data may be from W1 thru W7,
and the WD-A data may be from W8 thru W22. Etc, Etc. Since there are multiple
summing formulas on each worksheet, it is a huge amount of work to enter all
the time period ranges into each formula. My IDEA was to create a Table with
INDIRECT referencing. Then, for Park 1, to sum Cells W1 to W10, instead of
entering the formula =Sum(w1:w10), I would enter a formula similar to
=sum(INDIRECT(a101:a102)). [I know this is not the actual formula I want to
use] These "A" cells are just arbitrarily chosen cells. But in cells B101 and
B102, I would put the actual cell range I wanted to sum for the Weekday
Morning period (W1 and W10). I would also fill in the cells that
corresponded with the other 5 time periods (e.g. B102/B103 would have
reference cells W11/W19 for the WD-A period; B104/B105 would have W20 to W29
for the WD-E period; etc, etc.)
This way, I would only have to enter the indirect summing formulas for the
first worksheet. Then i could copy all these formulas to the other 25
worksheets for the 25 different Parks, and then just change the reference in
Cells B101 thru B112 to the correct range for each time period for EACH
worksheet. This would automatically change all the formulas on that worksheet
to the correct ranges.
My question - finally - is what is the exact structure of this Indirect Sum
formula that I'm trying to create? The one you gave me with your initial
response would work if there are "numbers" in Cells B101 thu B112. What I
want is a formula that will work if Cells B101 thru B112 contain a Cell
reference such as W1 or W10 or W11, etc.
Sorry the explanation is so long but I hope it is a bit clearer. Is it
possible to do what I want?
Thanks .... Joe

You didn't actually ask a question, but I think this is what you want:

=SUM(INDIRECT(A9&":"&A10))

However, if your sum ranges vary because you have different amounts of
data for some other criteria, you might be able to use SUMIF over your
complete range W1:W50. Perhaps you can explain why your ranges have
different lengths on different sheets?

Hope this helps.

Pete

If all of the columns in each worksheet hold the same information (but for
different parks), I would merge all of the data into one worksheet, with a
column for park number, and then use a PivotTables for reporting. If you are
not familiar with PivotTables, look here:

http://www.peltiertech.com/Excel/Pivots/pivottables.htm