How to increment the cell reference in excel

1
votes

I am calculating sum for multiple sheets in a workbook formula is as below

=SUM('Sheet1'!C11,'Sheet2'!C10,'Sheet3'!C11,'Sheet4'!C14)

The values being added are horizontal I tried adding $ sign infront of C11 it didnt work

I want to increment only Alphabet i.e C11 to D11 to AH11 C10 to D10 to AH10

=SUM('Sheet1'!D11,'Sheet2'!D10,'Sheet3'!D11,'Sheet4'!D14)

How do I achieve that?

1
excelexcel-formula
And how are you dragging this? Horizontal or downwards? – JvdV
dragging down vertically – howard
=SUM('Sheet1'!D$11,'Sheet2'!D$10,'Sheet3'!D$11,'Sheet4'!D$14) – RetiredGeek

1 Answers

0
votes

You could use (as your starting formula):

=SUM(INDEX(Sheet1!$11:$11,ROW(A3)),INDEX(Sheet2!$10:$10,ROW(A3)),INDEX(Sheet3!$11:$11,ROW(A3)),INDEX(Sheet4!$14:$14,ROW(A3)))

And drag down.