Variable in Excel Sum formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of rows with columns A1 to H1. Based on some criteria, I need
to sum from A1 to H1 or E3 to H3 or any possibility. If I store the
beginning cell in a cell like J1 or J3, how can I use the value of the cell
and not the cell itself? I want something like sum(actual value of
cell(J1):ending cell))
 
You can use index

=SUM(INDEX(A1:H3,1,5):INDEX(A1:H3,1,8))

replace 1,5 and 1, 8 with cells where you put the numbers
the above will sum E1:H1, 1 is the row number and 5 starting column (E) and
8 ending (H)

it's better to use this instead of indirect or offset since it is
non-volatile
 
=SUM(INDIRECT(J1&":H1"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 

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

Similar Threads

Excel formula, sum the value cell and if empty cell left blank 0
Copy/Paste formulas in merged cells 2
FORMULA PRODUCES WRONG RESULT 2
Summing the last 7 non-blank entries in a row of data 2
How to set cell value to emptry if three other columns are empty too 1
Sum Formula with variable range 2
Lookup & match 2
Set add function ignore #N/A 1

Back
Top