From: Seth.Schwarzkopf on 22 Mar 2010 16:37 i am using a sum if in a formula and i want to copy and paste it down my spread sheet. when i do excell changes all the cell references down 1 number. the only number in the sum if formula i want to have change is the critria, i want the range and sum range to be the same for all cells involved. i would just type it in each cell but i am looking at 300 pluss cells that i would have to change. is there a way to keep excell from changing all the cell references in a formulas From: Gary Brown on 22 Mar 2010 16:46 The '\$' makes formulas 'absolute. Example: =SUMIF(\$A\$1:\$A\$5,E1,\$B\$1:\$B\$5) only E1 (criteria) will change. The rest are LOCKED. \$A\$1 \$A means column A will stay Column A no matter what \$1 means row 1 will stay Row 1 no matter what You can combine these such as... A\$1 A means column A will change as you copy across columns \$1 means row 1 will stay Row 1 no matter what -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Seth.Schwarzkopf" wrote: > i am using a sum if in a formula and i want to copy and paste it down my > spread sheet. when i do excell changes all the cell references down 1 number. > the only number in the sum if formula i want to have change is the critria, i > want the range and sum range to be the same for all cells involved. i would > just type it in each cell but i am looking at 300 pluss cells that i would > have to change. is there a way to keep excell from changing all the cell > references in a formulas From: Per Jessen on 22 Mar 2010 16:51 You are obviously using relative references, where you have to use absolute references. =A1 is a relative refence =\$A\$1 is a absolute reference Your formula could look like this: =SUMIF(\$A\$1:\$A\$500,"=" & A1) Hopes this helps. .... Per On 22 Mar., 21:37, Seth.Schwarzkopf wrote:> i am using a sum if in a formula and i want to copy and paste it down my > spread sheet. when i do excell changes all the cell references down 1 number. > the only number in the sum if formula i want to have change is the critria, i > want the range and sum range to be the same for all cells involved. i would > just type it in each cell but i am looking at 300 pluss cells that i would > have to change. is there a way to keep excell from changing all the cell > references in a formulas  |  Pages: 1 Prev: HELP: how to locate objects on userform from VBA codeNext: Local cube files