Copy down formula by column but change referencing cell by Row

[leoncw96]

Hi everyone,

I'm rather new to excel and am currently facing an issue.
In Sheet 1 B1, I'd like to sum up Sheet 2 A2,A3,A4
When the formula is copied down to Sheet 1 B2, I want the cells Sheet 2 B2,B3,B4 to be summed.
Is there any way for the formula to be copied down?

Sheet 1

Sheet 2

 

[arthurbr]

Perhaps in B1

=sum(Sheet2!A$2:A$4)

and drag right
The relative referencing of the columns will allow them to change when dragging

 

[Peter_SSs]

Welcome to the MrExcel board!

Is there any way for the formula to be copied down?

Here are a couple of ways to copy down. I would choose the column B formula as that would still work if the column order in Sheet2 was not the same as the row order in Sheet1.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

leoncw96.xlsm
	A	B	C
1	China	45	45
2	Singapore	400	400
3	Malaysia	6500	6500
4	Denmark	35000	35000
Sheet1

Cell Formulas
Range	Formula
B1:B4	B1	=SUM(FILTER(Sheet2!$A$2:$D$4,Sheet2!$A$1:$D$1=Sheet1!A1))
C1:C4	C1	=SUM(INDEX(Sheet2!$A$2:$D$4,0,ROWS(C$1:C1)))