sumif
in [Programming]
|
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 <SethSchwarzk...(a)discussions.microsoft.com> 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 code Next: Local cube files |