I want to sum a variable number of rows which is in a column of numeric
information. For example, say the column has entries 1,2,3,4 and 5 in five
separate rows. I would like to be able to sum a number of contiguous entries
(e.g. sum the second, third and fourth rows, or sum the fourth and fifth
rows). This can obviously be done by creating a normal sum function (e.g.
=sum(A2:A4) for the first example above). However, if I want to vary where
the series is summed from (e.g. A2 or A3) - and the number of rows that are
summed (e.g. A2 to A4, or A4 to A5) - I need to go in and manually re-write
this formula. Is it possible to have a sum-type formula linked to two input
cells, where input cell one indicates where the summed series is to commence
from and cell two indicates the umber of rows that are to be summed?

Thanking you in advance.

Use INDIRECT(). Say A1 thru A30 contain:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

In B1 we put the starting row:
7
In B2 we put the number of rows:
5
In B3 we enter:
=SUM(INDIRECT("A" & B1 & ":A" & B1+B2-1)) which displays 45
--
Gary''s Student - gsnu200802


"CEGavinMcGrath" wrote:

I want to sum a variable number of rows which is in a column of numeric
information. For example, say the column has entries 1,2,3,4 and 5 in five
separate rows. I would like to be able to sum a number of contiguous entries
(e.g. sum the second, third and fourth rows, or sum the fourth and fifth
rows). This can obviously be done by creating a normal sum function (e.g.
=sum(A2:A4) for the first example above). However, if I want to vary where
the series is summed from (e.g. A2 or A3) - and the number of rows that are
summed (e.g. A2 to A4, or A4 to A5) - I need to go in and manually re-write
this formula. Is it possible to have a sum-type formula linked to two input
cells, where input cell one indicates where the summed series is to commence
from and cell two indicates the umber of rows that are to be summed?

Thanking you in advance.

Try something like this:

Column A are the values to sum

C1 = user input, the starting row number
D1 = user input, the ending row number

=SUM(INDEX(A:A,C1):INDEX(A:A,D1))

C1 = 5
D1 = 10

The formula will sum A5:A10

--
Biff
Microsoft Excel MVP


"CEGavinMcGrath" wrote in message
...
I want to sum a variable number of rows which is in a column of numeric
information. For example, say the column has entries 1,2,3,4 and 5 in
five
separate rows. I would like to be able to sum a number of contiguous
entries
(e.g. sum the second, third and fourth rows, or sum the fourth and fifth
rows). This can obviously be done by creating a normal sum function (e.g.
=sum(A2:A4) for the first example above). However, if I want to vary
where
the series is summed from (e.g. A2 or A3) - and the number of rows that
are
summed (e.g. A2 to A4, or A4 to A5) - I need to go in and manually
re-write
this formula. Is it possible to have a sum-type formula linked to two
input
cells, where input cell one indicates where the summed series is to
commence
from and cell two indicates the umber of rows that are to be summed?

Thanking you in advance.

I want to sum a variable number of rows which is in a column of
numeric information. For example, say the column has entries 1,2,3,4
and 5 in five separate rows. I would like to be able to sum a number
of contiguous entries (e.g. sum the second, third and fourth rows, or
sum the fourth and fifth rows). This can obviously be done by
creating a normal sum function (e.g. =sum(A2:A4) for the first example
above). However, if I want to vary where the series is summed from
(e.g. A2 or A3) - and the number of rows that are summed (e.g. A2 to
A4, or A4 to A5) - I need to go in and manually re-write this formula.
Is it possible to have a sum-type formula linked to two input cells,
where input cell one indicates where the summed series is to commence
from and cell two indicates the umber of rows that are to be summed?

In the csv file below,
A is the column of numeric information
B1 is the first row number of the sum (input cell one)
B2 is the number of rows that are summed (input cell two)
B3 is the sum.

Modify to suit.


-------------- cut here --------------
1,2,start row
2,3,count
3,"=SUM(OFFSET($A$1,B1-1,0,B2,1))",sum
4,,
5,,

Many thanks. Took me a while to figure out how to use your formula but have
cracked the issue that I needed to resolve.

Cheers.

"Gary''s Student" wrote:

Use INDIRECT(). Say A1 thru A30 contain:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

In B1 we put the starting row:
7
In B2 we put the number of rows:
5
In B3 we enter:
=SUM(INDIRECT("A" & B1 & ":A" & B1+B2-1)) which displays 45
--
Gary''s Student - gsnu200802


"CEGavinMcGrath" wrote:

I want to sum a variable number of rows which is in a column of numeric
information. For example, say the column has entries 1,2,3,4 and 5 in five
separate rows. I would like to be able to sum a number of contiguous entries
(e.g. sum the second, third and fourth rows, or sum the fourth and fifth
rows). This can obviously be done by creating a normal sum function (e.g.
=sum(A2:A4) for the first example above). However, if I want to vary where
the series is summed from (e.g. A2 or A3) - and the number of rows that are
summed (e.g. A2 to A4, or A4 to A5) - I need to go in and manually re-write
this formula. Is it possible to have a sum-type formula linked to two input
cells, where input cell one indicates where the summed series is to commence
from and cell two indicates the umber of rows that are to be summed?

Thanking you in advance.