Excel 2000: sum function automated?

[rhodyman10]

I run a report month to month on Excel 2000 on Windows 2000.

I want to make it easier to prep the report.

For example,
Cell b12 is the month total
Cell b13 is the quarter total.
The first month I want a total in cell b12 of b6 and b7 only.
The second month I want a total in cell b12 of b8 and b9 only.
The third month I want a total in cell b12 of b10 and b11 only.
Cell b13 will always be cells b6 through b11.

Also,
Cell b20 is the month total
Cell b21 is the quarter total.
The first month I want a total in cell b20 of b13 and b14 only.
The second month I want a total in cell b20 of b15 and b16 only.
The third month I want a total in cell b20 of b17 only. (for the third
month only one value, but I would like to keep cell b20 for
consistency).
Cell b21 will always be cells b15 through b19.

My report has many instances of these types of sums, across several
worksheets, with different numbers of cells in each group, such as the
examples above.

What I would like to have is something like this (if you can think of a
different way of doing this, I would be willing to consider it, also):
For the first month in the quarter:
Cell B12 would have a formula =sum(month1start:month1end)
Cell b20 would have a formula like =sum(month1start:month1end)

For the second month in the quarter:
Cell B12 would have a formula =sum(month2start:month2end)
Cell b20 would have a formula like =sum(month2start:month2end)

Ideally, I would like to edit/replace the formulas all at one time with
the same variable for the month start and month end. Please note that
the groups have different numbers of cells, as stated previously.

 

[flummi]

Without going into detail about why you are doing it that way (there is
certainly a reason), think about this:

(don't kow how you would determine the "current month", so I just put
it into a field)
All formulas: depending on your config replace ";" with "," where
appropriate.

a1 = the month you are in e.g. 1
a2 = the range to sum according to month in a1 (use any of the
following):

or: =ADDRESS(4+A1*2;1;4)&":"&ADDRESS(5+A1*2;1;4)
or: =ADDRESS(6+(A1-1)*2;1;4)&":"&ADDRESS(7+(A1-1)*2;1;4)
or: =SUM(OFFSET(A6:A7;(A1-1)*2;0))


a6:a11 = your values

a12: =SUM(INDIRECT(A2))
a13: =SUM(A6:A11)

Hope this helps.

Hans

 

[Guest]

I assume you have dates somewhere across the top? If so then checkout
SUMPRODUCT, something like:

=SUMPRODUCT(--(MONTH(B1:F1)=A1),B8:F8)
use A1 to input the month number

HTH
JG