2

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:
Sample image

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)
2
  • 3
    Basic Excel: put a $ sign before the part that you don't want to change.
    – Aganju
    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

2 Answers 2

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

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

2

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.

In your example that would make your formula:

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

Do note, this means you have to manually change the row counter because Excel just doesn't work that way. So you can do it per row, but then have to alter the first cell before transposing it to the right.

12
  • That will prevent it from getting changed when I drag it horizontally as well !!! Commented May 23, 2017 at 10:23
  • 1
    No it doesn't. You have to place the $ at the right place to make it work. I added the formula for you that you would need.
    – LPChip
    Commented May 23, 2017 at 10:24
  • Tried and tested. Adding $ before a value will ensure that the value is not changed by dragging in either direction. I want to change different variables in different directions. The formula that you have provided does not change values of $A1:$F1 or dragging horizontally. Commented May 23, 2017 at 10:28
  • Please have a look at this answer: superuser.com/questions/1211716/… Commented May 23, 2017 at 10:29
  • 2
    You may know excel but you seriously need to work on your ego inside-out. Do yourself a favor and copy it in excel and drag it either way. Perhaps you'll understand what I mean. Commented May 23, 2017 at 10:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .