CODEWITHSUNDEEP
excel
Roi patrick Florentino
Roi patrick Florentino

Reputation: 177

Formula on Cell changes when I drag it down in excel

I have a formula which goes like the one below

=COUNTIF(D10:D1000,Q13)

But everytime I drag it down D10 becomes D11. All I want to change is Q13 to Q14 to Q15 and so on but not the D10. So the above formula is for Q13 and in Q14 I want the formula to be

=COUNTIF(D10:D1000,Q14)

And for Q15

=COUNTIF(D10:D1000,Q15)

Notice that D10 doesn't change but Q+value does.

Kindly advice, Thanks.

Upvotes: 1

Views: 4327

Answers (2)

Jerry
Jerry

Reputation: 71538

Lock the range. This can be done by adding dollar signs before the references (or pressing F4 after typing/selecting the range:

=COUNTIF($D$10:$D$1000,Q13)

Though in this situation locking the row should be enough, since you're dragging down, the D won't change (you can add the dollar signs where required, or press F4 repeatedly until you get the right lock):

=COUNTIF(D$10:D$1000,Q13)

Upvotes: 4

DarkAjax
DarkAjax

Reputation: 16223

If you want D10 to stay the same specify an absolute reference using $, like this:

=COUNTIF($D$10:D1000,Q13)

Upvotes: 2

Related Questions