Define Sum range by using a cell reference

jimjam

New Member
Joined
Dec 5, 2005
Messages
43
Hi everyone,

Basically, I would like to set up a dynamic =sum formula, with where this range starts depends on the value in another cell.
Say in cell [a1] I have a value of 20.
In b1 is my forumla, which I want to sum up column c from 20 (as defined by [a1] ) to say 100.
Is it possible do do this?
thanks
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

=SUM(INDIRECT("C" & A1 & ":C100"))

would be one way of achieving this.

Best regards

Richard
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Another possibility:

=SUM(INDEX(C:C,A1):C100)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,849
Messages
5,544,647
Members
410,627
Latest member
georgealice
Top