# How can I keep the formula the same for an entire column?

#### jacatsos

##### New Member
I'm inserting new information at the top of my worksheet. Unfortunately the formula for what WAS row one: =SUMIF(TotalSales!\$B:\$B,A1,TotalSales!\$H:\$H) Will no longer work for row one because as more date is collected row 1 shifts down to row 2. Row two still looks good but there is no longer a formula for the new rows inserted.

To be clearer... I would like to have the following:
=SUMIF(TotalSales!\$B:\$B,A1,TotalSales!\$H:\$H) in cell B1
=SUMIF(TotalSales!\$B:\$B,A2,TotalSales!\$H:\$H) in cell B2
=SUMIF(TotalSales!\$B:\$B,A3,TotalSales!\$H:\$H) in cell B3

How can I keep that formula in row one as I keep inserting new rows?

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I'm inserting new information at the top of my worksheet. Unfortunately the formula for what WAS row one: =SUMIF(TotalSales!\$B:\$B,A1,TotalSales!\$H:\$H) Will no longer work for row one because as more date is collected row 1 shifts down to row 2. Row two still looks good but there is no longer a formula for the new rows inserted.

To be clearer... I would like to have the following:
=SUMIF(TotalSales!\$B:\$B,A1,TotalSales!\$H:\$H) in cell B1
=SUMIF(TotalSales!\$B:\$B,A2,TotalSales!\$H:\$H) in cell B2
=SUMIF(TotalSales!\$B:\$B,A3,TotalSales!\$H:\$H) in cell B3

How can I keep that formula in row one as I keep inserting new rows?

=SUMIF(TotalSales!\$B:\$B,A\$1,TotalSales!\$H:\$H) in cell B1

Thanks for the reply. I entered the formula you suggested but all rows (inserted above what was row 1) still have no formula so I still have to add them in one by one. There must be a way to keep that formula at the top but I don't know what it could be.

Thanks for trying.

Replies
0
Views
223
Replies
3
Views
248
Replies
7
Views
319
Replies
1
Views
101
Replies
9
Views
130

1,196,414
Messages
6,015,127
Members
441,874
Latest member
saustark

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back