Sum a dynamically-defined range

H

Hershmab

I have a series of cells containing the defining limits of a range, i.e.:
-sheet name
-top row number
-top column number
-bottom row number
-bottom column number

How do I sum (or otherwise operate on) the array so defined? I have tried
formulae containing combinations of various ADDRESS and INDIRECT functions,
but have not yet come across one that works.
 
J

Jacob Skaria

Try the below with

A1-sheet name
A2-top row number
A3-top column number
A4-bottom row number
A5-bottom column number


=SUM(OFFSET(INDIRECT("'" & A1 & "'!A1"),A2-1,A3-1,A4-A2+1,A5-A3+1))
 
L

Luke M

Using these references:
A1> -sheet name
a2> -top row number
a3> -top column number
a4> -bottom row number
a5> -bottom column number

This formula will genearte a summation of the dynamic range:
=SUM(OFFSET(INDIRECT("'"&A1&"'!"&ADDRESS(A2,A3)),,,A4-A2+1,A5-A3+1))
 

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