sumif

[Seth.Schwarzkopf]

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

 

[Gary Brown]

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

 

[Per Jessen]

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