Specific cell value should sum specific cells

[gregoremm]

Hi,
Newbie here. I tried looking in the forum for answers, but couldn't specify my problem in search terms. So any help would be greatly appreciated.

I have a table which outputs net profit on a yearly basis for 25 years.
thread1.JPG

Also on a different worksheet, I have the input cells.

This cell (A10) will contain either of the following values in either a list or manual input: 10, 15, 20, 25 (years).

My query is:
When the user inputs 10 in the first sheet in cell A10,
another cell, B10 should output the sum of 10 cells from sheet NET PROFIT (G13:P13).
But when the user inputs 15 in the same cell (A10), B10 should output the sum of 15 horizontal cells in sheet NET PROFIT (G13:U13). Same for 20 and 25.

Therefore, depending on the value of A10, B10 should calculate the same amount of cells from another sheet and output their sum.

I apologize if I didn't use correct terminology, I usually use excel for much simpler tasks.

Appreciate any help!

[samba_ravi]

pls attach a sample excel file with desired result

[sandy666]

try
B10: =SUM('net profit'!G13:OFFSET('net profit'!G13,13,A10))
max value for A10 is 15 (with your description)


If that takes care of your original question, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. Thanks.

[Special-K]

try
B10: =SUM('net profit'!G13:OFFSET('net profit'!G13,13,A10))
max value for A10 is 15 (with your description)


If that takes care of your original question, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. Thanks.

I think this is adding one column too many

Perhaps
B10: =SUM('net profit'!G13:OFFSET('net profit'!G13,13,A10-1))

[sandy666]

why?
=COLUMNS(G13:U13) = 15
ups, you are right

should be something like this:
B10: =SUM('net profit'!G13:OFFSET('net profit'!G13,13,A10-1))
and A10 cannot be 0

[Special-K]

Start with a blank sheet

put 5 in A10
in B10 =SUM(G13:OFFSET(G13,13,A10))
put the numbers 1-10 in G13: P13

Result is 21

Thats the numbers 1-6 summed but the value in A is only 5 numbers
Amend A10 to 1
Result is 3

Its adding one column too many

[gregoremm]

hey Guys! Thanks for the quick replies!

I am attaching a sample file, It is pasted exactly from the actual file therefore the cells are identical. (But not identical to the example I gave above).

So in the file, CELL E11 is the cell where a user will input the years of agreement: 5,10,15,20,25.
Cell H15 should calculate the number of cells in sheet DATA, starting G13 up to AE13.

Relevant example --> User inputs 10 in CELL E11 in sheet PROJECT REPORT, H15 shows SUM of (G13:P13) which means it counted 10 cells.
if User inputs 15 in CELL E11 in sheet PROJECT REPORT, H15 shows SUM of (G13:U13) which means it counted 10 cells and summed.

I hope this is a bit clearer now?

[sandy666]

cell, B10 should output the sum of 10 cells from sheet NET PROFIT (G13:P13).
But when the user inputs 15 in the same cell (A10), B10 should output the sum of 15 horizontal cells in sheet NET PROFIT (G13:U13).

and so on...
@Special-K
I changed formula before refresh thread and repeat your correction

[gregoremm]

Start with a blank sheet

put 5 in A10
in B10 =SUM(G13:OFFSET(G13,13,A10))
put the numbers 1-10 in G13: P13

Result is 21

Thats the numbers 1-6 summed but the value in A is only 5 numbers
Amend A10 to 1
Result is 3

Its adding one column too many

This is perfect!
Exactly what I want however the rows G13:U13 are in a different sheet. (See attached file)
How can I refer to it in your formula?

[sandy666]

like this:
=SUM(DATA!G13:OFFSET(DATA!G13,13,'PROJECT REPORT'!E11-1)) ?

[gregoremm]

like this:
=SUM(DATA!G13:OFFSET(DATA!G13,13,'PROJECT REPORT'!E11-1)) ?

Perfect!

I know my questions sound very noob.
You both solved perfectly.
Appreciate it!

[sandy666]

You are welcome

If that takes care of your original question, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. Thanks.

I got info from your file:

error.jpg

and your G13 value is a text so it will not be summed.
try this to check: =CELL("type",G13). If result is different than v you need to check format of this cell

[gregoremm]

You're right value isn't correct.
I'm just going to upload the whole file, mind taking a look?

[Special-K]

Try (am just guessing here)

=SUM(DATA!G13:OFFSET(DATA!G13,13,D11-1))

[sandy666]

try this one
=IF('PROJECT REPORT'!$D$11=0,0,SUM(DATA!$G$13:OFFSET(DATA!$G$13,0,'PROJECT REPORT'!$D$11-1,,)))

[gregoremm]

Masterpiece

That "-1" at the end saved it.

[sandy666]

these three things rather
=IF('PROJECT REPORT'!$D$11=0,0,SUM(DATA!$G$13:OFFSET(DATA!$G$13,0,'PROJECT REPORT'!$D$11-1,,)))

My fault, I was like a horse with flip-flops on my eyes