Forum Discussion
Range issue when I drag and copy SUMIF
I am having an issue with both ranges being off when I copy and drag the sumif formula. The formal in cell L2 reads =SUMIF(F1:F200,K2,G1:G200). I need the range to stay between rows 1 and 200, but when I drag and copy the ranges in the formula moves up by one for each cell the formula is dragged and copied to. For example, L20 should read =SUMIF(F1:F200,K20,G1:G200), but it reads =SUMIF(F19:F218,K20,G19:G218). How can I make the range remain fixed while the criteria changes as I drag and copy?
When you select the range then for the lets say F1:F200 press F4 key in your keyboard so that it makes is absolute reference and it will look like this
$F$1:$F$200 you can also manually put the dollar signs, but pressing F4 will do it for you easily.
You have not attached the data, but from what I can guess, you need to use the absolute reference in your range and mixed reference cell for the cell of criteriaSo your formula should be something like this
=SUMIF($F$1:$F$200,$K20,$G$1:$G$200)
a dollar sign in front of Column $A1 means column will not move but row will move
A$1 means row will not move but column will move
$A$1 means both column and row will not move.
A1 means both column and row will move
When you select the range then for the lets say F1:F200 press F4 key in your keyboard so that it makes is absolute reference and it will look like this
$F$1:$F$200 you can also manually put the dollar signs, but pressing F4 will do it for you easily.
You have not attached the data, but from what I can guess, you need to use the absolute reference in your range and mixed reference cell for the cell of criteriaSo your formula should be something like this
=SUMIF($F$1:$F$200,$K20,$G$1:$G$200)
a dollar sign in front of Column $A1 means column will not move but row will move
A$1 means row will not move but column will move
$A$1 means both column and row will not move.
A1 means both column and row will moveThank you so much for your quick response! The F4 tip works perfect and all my numbers are accurate! Thanks again!
