Forum Discussion
Formula repeating when dragging down
Hi all, When I drag my formula down, it is repeating the same formula (ie referencing the same cells as in the first row) rather than referencing the appropriate cells for the row. ...
AlexJamieson Try this in D2 and copy down:
=COUNTIFS($A$2:$A$31,">="&C2,$A$2:$A$31,"<"&C3)
You are my hero. Can you please explain the changes you made to the formula? What is the function of the "&" symbol?
AlexJamieson If you would be comparing a range to a fixed value, let's say 1300, you could use ">=1300" as the criteria. But when you want to link the value to a cell reference, you need to write it like ">="&C2. That will make the cell reference dynamic and change when you copy the formula down.
I am using this formula
=IF(L18=P8,0,IF(L18=P9,50,IF(L18=P10,100,"")))
I want to repeat in cells below but the reference cells (P8, P9 and P10) keep moving down 1
not sure I follow, but perhaps use absolute references, like $P$8, $P$9, $P$10.
- Thank you for taking the time to explain 🙂
