sumif
 

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

sumif
 

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

sumif
 

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