Summing a variable number of rows


C

CEGavinMcGrath

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.
 
Ad

Advertisements

G

Gary''s Student

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
 
T

T. Valko

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
 
Ad

Advertisements

C

CEGavinMcGrath

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top