Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have two identical tables on a sheet: one that contains text for reference, and a second that contains values.

I am trying to sum up some cells in the second table but using the addresses of cells in the first table to do so.

For example, if this is my data:

B1 contains apple  B11 contains 0.5   
B5 contains egg    B15 contains 1.5   
C7 contains ham    C17 contains 0.5 

is there a way I can write a formula =SUM(B1,B5,C7) but increment the row number of the arguments by 10 using some function?

share|improve this question
    
In the example you have provided it seems like you're trying to add text cells, is that right ? –  Darius Apr 3 '13 at 23:04
    
No I want the sum of the numbers –  Shakehar Apr 4 '13 at 0:11
add comment

2 Answers 2

up vote 3 down vote accepted

You can use Offset() like this:

=SUM(OFFSET(B1,10,0),OFFSET(B5,10,0),OFFSET(C7,10,0))

Mind you that Offset() is volatile and the formula will recalculate any time ANY cell is changed in the workbook. It's best to avoid too many Offset() functions because they can bring down performance on large workbooks.

share|improve this answer
add comment

Copy =SUM(B1,B5,C7) and paste that ten rows lower down.


I don't seem to understand the comment:

SU577676 example

On the right is a possible alternative. Copy the numbers to a separate sheet (placed to match text in existing sheet), group the two sheets and simply stick with existing formula/location.

share|improve this answer
    
that would work if the cells 10 rows above are empty –  Shakehar Apr 4 '13 at 15:13
    
I was guessing you wanted a total (cost/number of calories?) from simply picking 'ingredients' say using AutoSum but do not understand your comment - are you copying down over ten rows (I meant only copy into the tenth [one cell])? –  pnuts Apr 4 '13 at 15:40
    
I meant the cell where you write the original formula (from where you copy and paste the formula 10 rows below) , if that already contains come content you will not be able to write a formula there –  Shakehar Apr 4 '13 at 17:15
    
? I never specified where that should be (you did not either) and relied on you not to choose a cell that was already occupied! [Still makes NO sense to me :-).] –  pnuts Apr 4 '13 at 18:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.