Obviously you are trying to perform a conditional sum. By sorting you
group the records according to an attribute found in another column.
This attribute is the key to your problem.
Say you sorted by column C, and it contains values like "a", "b", "c"
etc. Presumably, in the sorted table, rows 49:52 contain "c" in column
C. Then you need the following formula:

=SUMPRODUCT(D2:D380*(C2:C380="c"))/SUMPRODUCT(--(C2:C380="c"))

This formula looks at the entire table and does not care where "c" is
in column C. It is unaffected by sorting. Modify accordingly (i.e. the
range references and the value you are looking for). If the value of
your criterion is numeric you do not need (in fact you must not use)
quotes.

HTH
Kostis Vezerides

mmcap wrote:
=SUM(D49:D52)/COUNT(D49:D52)

"Jason Lepack" wrote:

What is the formula that you are using right now?

mmcap wrote:
I use 2003 Excel. If I write a formula which works properly with the data
sorted by column A and then sort by a different column it no longer works.
There are two main groups that I need to sort by. Sometimes I need the data
sorted alphabetically (column A) and sometimes I need it sorted by groups
(column G). If I write a formula in cell (J50) while sorted alphabetically
and then sort by groups the formula in cell (J50) might move to cell (J40)
and the data that is referenced by that formula could be scattered anywhere
on the sheet so the formula is no longer relevant. Is there any way to make
the formula adjust to where it will still refer to the same data even if the
data and the formula cells move after being sorted?