Slightly confusing question on how the find the sum

**beamthegreat** · 06-08-2013, 04:30 PM

Is it possible to make excel find the total amount of item in the box from the value we input into a cell, added from box #1 first to box #6? This might be confusing so consider the scenario below:

box #: 1, 2, 3, 4, 5, 6
amount of item in box: 1, 2, 4, 8, 12, 16

Would it be possible to make excel add up the sum from box #1-4 simply by typing 4 or box #1-6 by typing 6 and so on?

So this should appear:

value: 1, 4, 6
total amount of item: 1, 15, 43

Basically, if the value is 1 then we find the sum of box #1 so its 1, and if the value is 4 then we find the total amount of item from box #1 to box #4, so 1 + 2 + 4 + 8 = 15, and so on.

How do I do this? What do I write into the excel to make this? Thanks.

**martindwilson** · 06-08-2013, 04:36 PM

with those values in a1:f1
in a2 enter the number you want so sum
in a3
=SUM(OFFSET(A1,,,,A2))
so if a2 contained 5 you would get 27

**beamthegreat** · 06-08-2013, 04:46 PM

Thanks for the response but doesn't work. Assuming the values are in a1:f1, when I type in: =SUM(OFFSET(A1:F1,A2)) an error comes up. Could you be more specific, I am a noob when it comes to excel.

**beamthegreat** · 06-08-2013, 04:53 PM

Ignore the above I misread your post. What do I do if the values are in a1:a6?

**martindwilson** · 06-08-2013, 05:06 PM

=SUM(OFFSET(A1,,,C1)) where c1 contains 1 2 3 4 5 or 6

**beamthegreat** · 06-08-2013, 05:17 PM

Thanks! But I have another problem, if the value of the height is 0, it comes up as an #REF! error so its there anyway to force this value to 0 when this error shows up? I'm trying to add it with another column but it doesn't work if the first value is an error, if you get what I mean.

Basically, this is what I'm trying to do: =(SUM(OFFSET($R$35,,,R21,)))+(SUM(OFFSET($S$35,,,S21,)))) but if R21 is 0 then the cell turns up to be an error, even though the second part functions perfectly fine alone.