# Change formula on dragging

I have a special condition. I have three variables in my formula.

``````=COUNTIF(A1:F1,A84)
``````

Now, when I drag it vertically I want to change the value of the first parameter i.e. A:F and on dragging horizontally I want to change the value of second parameter i.e. A84. I don't want to change column. Ever.
For example:

How do I do it?

This answer solves the issue for another formula. Perhaps the solution would look something like this:

``````=COUNTIF(INDEX(\$A:\$A,COLUMN(A:A)):INDEX(\$F:\$F,COLUMN(F:F)),\$A85)
``````
• Basic Excel: put a \$ sign before the part that you don't want to change. Commented May 23, 2017 at 10:19
• @Aganju: That will prevent it from getting changed when I drag it horizontally as well !!! Commented May 23, 2017 at 10:23

``````=COUNTIF(INDEX(\$A:\$A,COLUMN(B:B)):INDEX(\$F:\$F,COLUMN(B:B)),\$A86)
``````

Another solution would be doing the same using `Indirect()` function.

When excel automatically changes cell references, it will keep a cell locked if you add a \$ in front.

So To lock the reference of A1 to its cell regardless of horizontal or vertical movement, you use \$A\$1.

If you want to make the rows change, but not the columns, You'll change the A1 reference to \$A1.

If you want to make the columns change, but keep the rows the same, you change the A1 reference to A\$1.

``````=CountIf(\$A\$1:\$F\$1,\$A84)