Ctrl D with formulas

N

NEHicks

When I put a formula in B87 [=SUMIF(B5:B81,"3A",H5:H81)*24] and then select
B87:B94 and use the Ctrl D function, it will copy the formula but the range
will change by 1 line each time so that B88 will start with the range
(B6:B82, "3A", H6:H82), etc. By the time I reach B94, the formula range is
completely off and I have to go into each formula and manually change the
range back to what it should be.

What am I doing wrong? I need to freeze the formula so that it keeps the
same range.
 
M

Mike H

Hi,

I'm not going to ask why you want the same formula several times but try this

=SUMIF($B$5:$B$81,"3A",$H$5:$H$81)*24

Mike
 
H

Harlan Grove

NEHicks said:
When I put a formula in B87 [=SUMIF(B5:B81,"3A",H5:H81)*24] and then select
B87:B94 and use the Ctrl D function, it will copy the formula but the range
will change by 1 line each time so that B88 will start with the range
(B6:B82, "3A", H6:H82), etc. By the time I reach B94, the formula range is
completely off and I have to go into each formula and manually change the
range back to what it should be.
Click to expand...

You could make the row 'absolute' (unchanging when the formula is
filled or copied and pasted) by inserting $ before the row numbers in
the cell addresses. In other words, you could change the B87 formula
to

=SUMIF(B$5:B$81,"3A",H$5:H$81)*24

When you fill this down into B88:B94, the formula in B88 would be the
same as the formula in B87, namely,

=SUMIF(B$5:B$81,"3A",H$5:H$81)*24

But it's unclear why you'd want exactly the same formula in B87:B94
rather than the SUMIF formula in B87 and =B87 in B88, =B88 in B89, etc.
 
G

Gord Dibben

Add the $ signs

=SUMIF($B$5:$B$81,"3A",$H$5:$H$81)*24

See help on absolute and relative referencing.


Gord Dibben MS Excel MVP
 
Top