Sorting with calculations

[David]

I have a simple goal, but can't figure this out. Let's say you have a
column with numbers:

1
2
3
4
5

I wish to sum the numbers "4" and "5" using the formula "=sum(A4:A5)"
which adds up to 9. Now I resort the number list to read:

5
4
3
2
1

Keeping my simple sum formula "=sum(A4:A5)" the two numbers it
references are now 2 and 1, adding up to 3. The problem is that even
after I sort, I need the formula to know that I want to add "4" and "5"
not "2" and "1".

How can I do this?

 

[Pete_UK]

One way of doing it is to reproduce the numbers in a separate column
with a formula =A1 copied down and then CTRL-H to change A to A$, so
that you have A$1, A$2, A$3 etc down the column. This second column can
be sorted and the original formula still shows the result of 9 as the
original data does not move. Column A can be hidden if necessary.

Hope this helps.

Pete

 

[David]

Hmm, I appreciate the reply, however their is a disconnect (and likely
with me). I will restate my problem, with a more practical example,
vs. the hypothetical described earlier. I have a list of customers
(company names) in column A, a matching list of dollar values
associated with each customer in column B, and a list of US States in
column C.

I have sorted by the largest dollar value to the smallest. I have a
calculation that adds 5 different dollar values that spread across the
dataset. Now I wish to sort my dataset by State (in alphabetical
order). Doing so completely throws off my calculation.

This is what I am trying to fix. I hope this is more clear.

 

[Bernard Liengme]

Are you saying that if you have values (eg 100, 200, 300, 400, 500) in
specific cell (eg B3, B7, B9, B12, B20) before the sort, then after the
sort you want the came values (not cells) to be summed?
One way would be to convert the formula to a value. So you type =
B3+B7+B9+B12+B20, then you use Copy followed by Edit | Paste Special ->
Values. Now you can sort and the results is unaltered.
Failing this, can you reveal the criteria used to select the 5 dollar
values?
best wishes

 

[David]

Thanks everyone. I decided to add a column into my spreadsheet which
included attributes related to the criteria used to select the dollar
values. I then used the "sumif" function to develop my calculations.
Now, no matter what the order of the dataset, my calculations remain
accurate.

Thanks again!