Another Advanced Cell Reference

[Popik]

Hello,

A follow-up to my previous question. I would like to sum from cell A1
through cells in row 1, column A + indirect(example number). I am unable to
use the string =sum(A1:address(example code)) because the sum function will
not allow address() as an argument. Suggestions?

Thank you for your help.

 

[Max]

One way is to try it like this

In say, B1: =SUM(OFFSET(A1,,,10))
will return the sum of 10 col cells starting from the anchor cell A1,
ie equivalent to: =SUM(A1:A10)

Adapt the anchor cell and the "10" to suit

 

[Ed Ferrero]

Hi Popik,

In general, use a formula like;
=SUM(INDIRECT(ADDRESS(StartRowNo,StartColNo)) :
INDIRECT(ADDRESS(EndRowNo,EndColNo)))

So, to sum from A1 to O1
=SUM(INDIRECT(ADDRESS(1,1)) : INDIRECT(ADDRESS(1,15)))

Of course, you can use cell references instead of the values shown above.

Ed Ferrero
www.edferrero.com

 

[T. Valko]

Another one:

=SUM(A1:INDEX(A:A,n))

Where n is the numbers of cells to sum. For example:

n = 5 =SUM(A1:A5)
n = 10 =SUM(A1:A10)